Tag Info

Hot answers tagged

14

Your problem boils down to access control. The first defense I'd propose is to simply deny access to the untrusted users. If they can't get into the database, they can't query the database and get at the sensitive data. If they must be allowed to access the database server, you can look at either explicitly granting them read permission to the tables they ...


9

My article will help if you set it up in advance, but not when the event happened in the past and you didn't have any kind of auditing mechanism set up. There is still hope, though. Let's say I did this: CREATE LOGIN flooberella WITH PASSWORD = N'x', CHECK_POLICY = OFF; This information is in the default trace under EventClass 104 (Audit Addlogin Event). ...


6

The only way to use encryption to protect the data against your own administrators/IT is when the user enters the decryption password him/herself, every time it queries the data. If your application presents the user with a password dialog and then issues an OPEN SYMMETRIC KEY ... DECRYPTION BY CERTIFICATE ... WTIHT PASSWORD ... (or some equivalent) to open ...


6

Generally speaking, adding AD groups to database roles and granting the permissions to the roles gives you some separation of purpose. In that case you would use them as: AD Group = a set of logins that share the same database access needs. Role = a set of permissions that define a set of rights to be shared by logins. The advantage is that the role can ...


6

This is a prime reason why different applications should be using different principals to authenticate on. If you use Login1 for 5 applications, then it would become a nightmare to manage security if you ever need those 5 different applications to have different security schemes. My recommendation? Have a separate login for both of those applications. ...


5

You don't have to perform any task (destructive or non-destructive) at all. You can use the built-in function IS_SRVROLEMEMBER to find if a certain LOGIN is a memeber of the sysadmin server role: SELECT IS_SRVROLEMEMBER('sysadmin','<LoginName>'); Note that for roles other than sysadmin the result will be positive (=1) for implicit membership ...


5

This means that database users have no matching server logins. That is, each database sys.database_principals has no match in sys.server_principals For Windows logins this is easy. This generates your missing CREATE LOGINS USE MyDB SELECT 'CREATE LOGIN ' + QUOTENAME(SUSER_SNAME(sid)) + ' FROM WINDOWS' FROM sys.database_principals DP WHERE ...


5

Make sure the application connects to the server using a login that has been given only read permissions (give it the db_datareader role in the database to allow reading all tables), and you should be in good shape. The easiest way to prevent changing data is to ensure the user doesn't have permission to change anything. Be careful about granting execute ...


5

First, you can't prevent a sysadmin from doing anything. :-) Any login that is a member of the sysadmin fixed server role operates outside the permissions system - they can do anything. My first thought was to use a DDL trigger to stop the restore. Unfortunately, restoring a database is an operation that does not cause any triggers to fire (see this Connect ...


5

Greenstone Walker is right. If you are granted SA rights, there really isn't much to do there to prevent it. I like that you are worrying about protecting from your ability to make such a mistake. I've seen people burned by an "oops" restore before. Not pretty. There are potentially a few things you can do, though. Your milage may vary but some ideas to get ...


4

In my view Windows Authentication is still more secure. Just. With windows authentication you can allow a group of people (via a Windows Group) to have access to the database. Only those users can use an application, any application, to connect to the database. If you use a SQL Server account then anyone who knows the credentials (and invariably these ...


4

Granting permissions on the schema (e.g. dbo) will cascade to all the objects in that schema. For individual exceptions you can just list those explicitly: GRANT SELECT ON SCHEMA::dbo TO [role]; GO GRANT INSERT, UPDATE --, DELETE ON dbo.table_they_can_write_to TO [role]; DENY SELECT ON dbo.table_they_cannot_read TO [role];


4

Quotes taken from the documentation. If you have the SUPER privilege, you can specify any syntactically legal account name. If the account does not actually exist, a warning is generated. Being able to specify security accounts other than the view's creator is a key piece of using DEFINER, which you seem to have missed. When a view has been ...


4

The system privileges that relate to the object types (CREATE TABLE, VIEW, ...) can be divided in two groups: Those that lack the keyword ANY are only effective on the user's own schema. Those with the keyword ANY (DROP ANY TABLE, EXECUTE ANY PROCEDURE, ...) are effective on all schemas. Consequently, you should restrict the ANY privileges to ...


4

I have not personally tried this, but have a look the the Idera Permission Extractor: Free Tool SQL Permissions Extractor If you need a sql solution, there is nothing built in. However, you can query the sys.server_permissions and sys.database_permissions tables. They contain the information needed to build the grant statements yourself. ...


4

I found below script in my script repository that will help you. I have used it many times and its a life saver especially when you want to transfer database roles and object permissions from one server to another : Credit goes to the original writer : Bradley Morris --Script to Reverse Engineer SQL Server Object User Permissions --Written By Bradley ...


4

These logins are created from a certificate. In fact, if you run the following query: select name, type_desc from sys.server_principals where type = 'c'; You will see that they are of type CERTIFICATE_MAPPED_LOGIN. They are used typically to sign code. And you cannot use a certificate mapped login to connect with SQL Server. Please see this ...


4

I would first question why they need direct access to the database. You might ask your manager or legal department if the security policy for the company allows granting this type of access. Is it really needed if they are just going to execute a stored procedure on a regular basis. As you stated this is going to be on a regular basis I would setup an SSIS ...


3

Using a batch file and SQLCMD, you can execute the script as below : cls echo off set DbServer=Server_name\InstanceName set MyLogin=sa set MyPassword=StrongPassword set MasterDbName=master set DbName=AdventureWorks set SQLCMDPath=C:\Program Files\Microsoft SQL Server\90\Tools\binn set SpScripFilePath=C:\ScriptFolder set SpScripFileName=test.sql echo ...


3

As you describe it, it's not going to work. The T-SQL script needs to run on a SQL Server which means that it needs to be run in an appropriately-authenticated session. One option would be to write the TSQL script, and then provide it with a VBScript or CMD file or PowerShell script that'll launch the TSQL at the correct server with the right credentials.


3

See Signing an activated procedure for an example of how to properly sign an activated procedure exactly so it it can leverage VIEW SERVER STATE privilege from an activated procedure. The steps are: inspect the procedure code to ensure that you trust it change the procedure to have an EXECUTE AS OWNER clause create a certificate with a private key in your ...


3

Whilst liasing with Microsoft on a different issue (paid support request) i happened to ask them about this & they confirmed the unhashed password is passed to the remote server but the mechanism in which the SQL Engine does this is "hidden and cannot be captured" - but suffice to say, the login is not done using the hash. Here is there full response: ...


3

Well, after a dozen phone calls to Microsoft via Paid Support & a 1.5 hour conversation with their connectivity team & 3 weeks of traces, procmon analysis and what not i was AMAZED to be told that this is a known issue: https://connect.microsoft.com/SQLServer/feedback/details/753426/dbmail-fails-when-using-a-linked-server-query Basically, Microsoft ...


3

No, there is no reliable way to prevent the local administrator (or a domain admin with the ability to grant themselves local admin on a machine) from accessing the raw PostgreSQL data files, starting/stopping the server, changing the service account password, changing pg_hba.conf so they can log in to the server, etc. As stated in the comments, if you ...


3

For reference "Denali" is SQL Server 2012. With regards to "end user confusion", I am not all that concerned with whether a end user is confused or not with regards to SSMS. Microsoft did not develop this tool for the normal end user, but for the database administrator and/or a user that had to manage a database. Therefore there will be a learning curve with ...


3

It does not open up directly to an "attack". It just means that any user from Database 1 (Kdb) can also access database 2 (Ydb). What's usually more critical is, when you have users with DDL-Permissions (create views, procedures) - they will also be able to access objects in database2. Maybe even more, than plain guests can. That depends on the object owners ...


3

You didn't mention what version of SQL so my answer is for SQL2008R2 in particular. Right click your DB in SSMS and select generate scripts then in the wizard that follows fill in as follows: Next Ensure "Script entire database and all database objects" is selected Tick users Next Click Advanced Find "Script Object-Level Permissions" and change to True ...


3

Look at the encryption hierarchy found in this TechNet article. This document from MS shows the entire hierarchy. You can see that the Master Key is created from the Windows DPAPI service and is used for: -DB Master Key -Certificates -Symmetric Keys -Asymmetric Keys -TDE -Transact SQL Encryption Functions -Passwords (I'm not 100% on this, but it ...



Only top voted, non community-wiki answers of a minimum length are eligible