SQL Server security via TSQL

Question

I want to construct a transact sql script that will stop specified people from running certain commands against all databases:

drop database, drop table or preferbly drop *

delete

update

Is this possible? The user will already have access to the server.

Note : I am not trying to develop a security model for a server, or to prevent a malicious attack. This is an existing server where people may have a range of access rights through various Windows groups they belong to. I just want to know if there is a quick safeguard to prevent people from mistakenly running a command on the wrong server.

Answer 1

(caveat, this is per-database; I don't know of anything server-wide, since the database is the main standalone unit)

Presumably your user isn't the owner of the schema (or dbo)? In which case, they already shouldn't have access to, well, anything unless you GRANT it. So don't GRANT the access they don't need, REVOKE any access you have granted incorrectly, and DENY anything that you absolutely don't want them ever being able to do.

See also MSDN.

Answer 2

The only way I know to limit specific rights reliably is to use stored procedures. Very secure, but not dynamic.

Stored procedures provide significant benefits when it comes to security. By using a stored procedure, you can grant permissions to certain users to access data, reducing the immense coding that you need to do in your client applications. This is one of the best ways to control access to your data.

http://www.wwwcoder.com/main/parentid/191/site/4004/68/default.aspx

Answer 3

you cannot really check if you see drop commands because someone might do something like this

just imagine this has exec instead of print

print ( convert(varchar(50), 0x64726F7020646174616261736520616263))

you could give them read access only and give exec access to stored procs only

you can also use DDL triggers http://msdn.microsoft.com/en-us/library/ms190989.aspx

Here is an example

CREATE TRIGGER safety 
ON DATABASE 
FOR DROP_TABLE, ALTER_TABLE 
AS 
   PRINT 'You are not allowed to drop or alter tables!' 
   ROLLBACK
;

Answer 4

Cindi - look into the DENY permissions command in order to gain fine-grained control over the capabilities of your users.

I also have to ask a deeper question, though. Why would these people have SQL command line access to your databases? That is, if they shouldn't even be able to delete or update records, what possible reason could they have for being given access to a SQL command line to begin with?

I really don't think that you need to know how to DENY permissions. You need rethink your overall data access strategy so that you avoid giving these people an account at all. It sounds like you need to gate their access to the data solely through a front-end application.

Update: The one scenario I can envision where this makes sense is if you have some folks who are only generating reports. In this case, you should create an account with a blanket DENY status and then GRANT permission only on Select and on specific stored procedures (only the ones that pull data useful for reports).

Answer 5

While I use SQL Server where I presently work, I don't deal much with the security side of it. I would imagine you can do the same for SQL Server as in Oracle (where I have more experience).

Revoke all access, then add back in just the access each user needs. Use user groups to help cluster them together and for ease of permission management.

it looks like the users you want to restrict should only have:

• whatever privilege is required to connect to the server (and possibly select the database)
• select access to all tables and views in a certain schema
• insert access to all tables in a certain schema
• possibly execute privileges on certain stored procedures in a certain schema

nothing else should be granted.

Answer 6

If you're on SQL Server 2005 or 2008, the best answer is one that's already been given, and that's DDL triggers. A properly written DDL trigger will stop even someone who has sysadmin rights from performing any sort of DDL operation. A sysadmin can disable a trigger to perform work, or the trigger can be written to allow certain people to perform work, so you still have the option to make modifications as required.

If you're on SQL Server 2000 (or below), your only recourse is to check security permissions for each login/user. Ultimately this is something that needs to be done even if you're on SQL Server 2005 or 2008, but there isn't a shortcut in the previous versions.