SQL Server Performance Forum – Threads Archive

Allow Someone to Restore DB with Granting DBO?

We have a consultant who would like to restore a test database on an as-needed basis, however I am hesitant to grant dbo permission to his id. Is it possible to give someone the ability to restore a SQL 2005 database without that person having dbo permission? I’m guessing the answer is no. Dave

Hi, Yes, it is possible. Members of the dbcreator fixed server role can create, alter, drop, and restore any database
Madhu

I guess that’s a little more restrictive then dbo. Thanks, Dave

Try with db_backupoperator also if you have issues with DBCREATOR server role.
Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

I tried db_backupoperator and unless I was doing something wrong, it only allowed the id to perform database backups, not restores. I also thought about taking advantage of the "run as" feature in a SQL Job. Thanks, Dave

Hm, I will try on test one and come back to you. I’m sure this was good when I tried earlier. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

I may be doing something wrong. Even when I grant the login id DBO permission I receive a message indicating the user does not have permission to restore the database. How is that possible? The login id is DBA
DBA is a user of the database, Test1
DBA is a member of db_owner in the Test1
Default schema is dbo
Default database is Test1
DBA is a user of msdb
DBA is a member of SQLAgentUserRole The error occurs if I run a scheduled job owner by DBA to restore the database or if I try to restore the database by right-clicking and choosing restore. Any idea what I’m missing? Am I suppose to be doing something with "Effective Permissions" when granting DBO access to a database? Dave

Satya, It works if I grant dbcreator permission. Why is dbcreator permission required if the id is a member of db_owner and the Restore Database statement is NOT using WITH REPLACE. Dave

Hi, To create a database on a server you have to have Server Level Permission. Database level permission is not enough. DBCreator is a server level role where as db_backupoperator is a Database level role. SO you can not restore a database when the role is db_backupoperator. DBO is database owner to the particular database. To create a new database you should be member of either DBCreator or SysAdmin. Refer Server Level Role and Database Level Role in BOL regards Madhu

Dave
As explained above with DBCREATOR it shouldn’t be a problem.
I’m still trying to recreate the scenario on my test bed, the result is same as yours…failing to restore. I will come back to you once i have my options. BTW, if you’re using Windows authentication and with DBCREATOR role there shouldn’t be much problem for this consultant to access the server.

quote:Originally posted by DBADave Satya, It works if I grant dbcreator permission. Why is dbcreator permission required if the id is a member of db_owner and the Restore Database statement is NOT using WITH REPLACE. Dave

Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

Madhu, I’m not using the "WITH REPLACE" option so according to BOL it should work. In BOL under "New Information – SQL Server 2000 SP3." is a chart indicating that members of db_owner can execute the "Restore" command. BOL also says "If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database." Perhaps only the actual owner (dbo) can restore a database and not members of db_owner, but that doesn’t make much sense to me if it is true. Dave

Is this a SQL 2000 or 2005 related questions? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

2005. This isn’t an issue in 2000.

Someone pointed me in the right direction. I searched BOL again and found the statement below. RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions. Thanks, Dave

]]>