3

We've been having issues with Databases being DROPed or RESTOREd WITH REPLACE with the incorrect backup etc...

I'm developing a plan right now on how to restrict access and I have some questions.

I'm familiar with SSMS but still am not super experienced with it, especially when it comes to "permissions".

We want everyone to be able to run querys/inserts/etc... on the databases and be able to take backups.

However, ideally we want them to have to submit a ticket to either restore a database or drop a Database completely. (And let me know if there's maybe something else I'm missing).

All the SQL Server instances are kept on a server. Lets call it TEST. On server TEST there are instances of SQL 2005/2008/2012/2008R2. Within server TEST there are separate Windows Server security groups; DEVS, QA, DBA, MANAGERS, etc.

Is there a way to restrict access to groups other than the DBA Group? What would be the best way to do this? We would want it restricted for the whole instance (Aka 2008/2012 etc..) instead of on a per database basis.

1
  • As a professional DBA, I would advise against having non-DBA's execute CREATE DATABASE except where there is a highly-defined process surrounding that work. CREATE DATABASE (and all server-level DDL) should be performed by knowledgeable persons. – Hannah Vernon May 9 '14 at 19:05
3

In SQL Server Management Studio, expand the 'Security' node under the server instance in question.

Add the Windows Group in question to the Logins node.

Under the Server Roles section, leave the restricted groups, such as DEVS, QA, etc, as Public, and assign them access to the desired databases via the User Mapping node.

For the DBA group, assign them the sysadmin server role.

This will prevent databases being dropped / replaced by everyone except members of the DBA group.

Look at SQL Server Books Online for further details: http://msdn.microsoft.com/en-us/library/aa337552.aspx

In order to allow users to create databases, you could add them to the dbcreator role. Unfortunately, this role also allows members to DROP DATABASE, etc, as documented at http://msdn.microsoft.com/en-us/library/ms188659.aspx

A server-level DDL trigger could be created to prevent users performing DROP DATABASE such as:

USE master;
GO
CREATE TRIGGER DropDB ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
    IF COALESCE(IS_MEMBER('dba'),0) = 0 ROLLBACK;
END
GO
ENABLE TRIGGER DropDB ON ALL SERVER;

However, this does not prevent the user from executing RESTORE DATABASE commands.

Additional Info

Your requirements can be resolved by creating a stored procedure that allows users to create databases, whilst preventing them from performing the other actions allowed by membership in the dbcreator server role.

Details about how to create the stored procedure in a secure manner have been provided by @PaulWhite in his detailed answer here.

6
  • This is what I was thinking, just wanted to be sure. – user1031 May 9 '14 at 16:03
  • 1
    Keep in mind that if any user is a member of multiple groups, the group with the most access wins. Also, if a user has his own logon with less restrictive rights, that takes precedence. – Hannah Vernon May 9 '14 at 16:12
  • Can public users still "create" databases? Because I want them to be able to create....but just not delete them . – user1031 May 9 '14 at 17:31
  • Members of the Public role cannot create databases. Members of the dbcreator role can create databases, however they can also drop them, among other things, which is clearly not going to cut it. You likely need to create a server-level DDL trigger to prevent DROP DATABASE. I'm updating my answer to reflect that. – Hannah Vernon May 9 '14 at 17:53
  • I was going to provide a way to have underprivileged users create databases, however the code generates an error. See dba.stackexchange.com/questions/64868/… for that info. – Hannah Vernon May 9 '14 at 19:02
3

Server roles are made for this.

Read this for the details http://msdn.microsoft.com/en-us/library/ms188659.aspx

The dbcreator role looks like what you are looking for.

4
  • Question: If I edit a server role for the instance (aka 2005/2008) will it change it for all the databases? Also if I have the server role in the instance, but not in the individual databases....will that database still have the same server roles as the SQL Instance? – user1031 May 9 '14 at 16:11
  • A server-role is server wide. Database roles are database-specific. However, being a member of the server-role Public does not automatically grant you access to individual databases. – Hannah Vernon May 9 '14 at 16:54
  • @MaxVernon , gotcha. So what if I change a server role on the server......does it change it on the Databases too? – user1031 May 9 '14 at 17:21
  • Server roles only exist at the server level; any change made to them is reflected system-wide. – Hannah Vernon May 9 '14 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy