SQL Server permissions to create, restore, delete only some databases

On MS SQL Server 2008 R2, I need to have user which can create, restore, DBCC CHECKDB and then drop databases. But he must not be able to access or delete specified databases on server (security reasons). Is this setup possible?

Yes it is possible through a couple permissions.

First - to create a database - You would need to grant the server level permission Create Any Database. This permission does just what it sounds like - the power to create a database. Note this is not giving the fixed server role of dbcreator to the login, since that fixed server role gives alter/delete permissions on any database. Create Any Database allows the login to have that power over databases they own, only. This will also give the ability to restore.

(So by doing "Create Any Database" ONLY - you are giving the ability to create a new database, but avoiding the ability to Drop or Alter ANY database that you'd get by using the dbcreator fixed server role)

To grant this "Create Any Database" permission -

use [master]
GO
GRANT CREATE ANY DATABASE TO [LoginName]
GO

For the ability to run DBCC CHECKDB and to drop databases - the fixed database role db_owner is sufficient. This grants all of what you are requesting. Please Note: You are also granting this user the ability to select, delete, truncate, update and insert with db_owner. I like to think of this as sysadmin within a database.

You only will apply this permission to the database you intend to have this ability on. This will allow you to control which databases the user can drop, restore or checkdb on. Any databases that you have not given the user these permissions on will be safe from this user doing these actions on

To do this you would first map the login to a database user:

USE [DatabaseName]
GO
CREATE USER [UserName] FOR LOGIN [LoginName] 
GO

And then add that user to the db_owner role (Works in SQL Server 2012 and on):

ALTER ROLE [db_owner] ADD MEMBER [frank]
GO

For SQL Server 2008 you would have to use the system stored procedure to add a role member as a commenter below rightly pointed out:

EXEC sp_addrolemember 'db_owner', 'frank';

You can also do all of this through the GUI. The login information you would do through the security folder in SSMS at the instance level: Right click on the login -> Properties -> Securables. For the database level, you would do it through the security folder at the database level: right click and select new user -> choose the login you wish to user from your list of server logins/give the database user a name -> go to the membership tab to select the role membership.

SQL Server's help system, Books Online, is a fantastic resource for most permissions questions also - if you determine you need to assign other permissions. Simply do a search for the T-SQL command you wish to give permissions to and there is typically a Permissions section for that command in the article letting you know what permissions are required to do this action. You can look at the article for DBCC CHECKDB for an example - about 7/8 of the way down the article is the permissions section.

StackExchange Database Administrators Q#29340, answer score: 27