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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
Yes it is possible through a couple permissions.
First - to create a database - You would need to grant the server level permission
(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 -
For the ability to restore, to run
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:
And then add that user to the
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.