November 2, 2006 at 12:09 pm
I have a consultant who needs to be able to restore a database. I can only do this by granting sysadmin or dbcreatore permission. I've tried to get it working by adding the user to db_owner on the database, but receive "permission denied" when I attempt to restore the database while connected as the user. I thought a database owner has permission to restore a database. The database is question is owned by dbo and the user is a member of db_owner.
Any suggestions?
Thanks, Dave
November 2, 2006 at 12:15 pm
User needs to have CREATE DATABASE permissions... add them to the dbcreator server role.
November 2, 2006 at 12:17 pm
I forgot to mention I am not using "WITH REPLACE". In SQL 2000 I only need to grant dbcreator if the database is being replaced. Not sure why 2005 would require dbcreator if I am not replacing the db.
November 2, 2006 at 12:33 pm
You shouldn't. They should only need CREATE DATABASE. From the master database you can execute something like this:
GRANT CREATE DATABASE TO [login]
And he/she should be good to restore the db without being a member of dbcreator. I haven't tested this though so YMMV.
November 2, 2006 at 12:47 pm
I'm still confused as to why it's needed. 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. " My only thought is 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.
Thanks, Dave
November 2, 2006 at 1:02 pm
Might just be bad/confusing documentation.
November 2, 2006 at 1:52 pm
Isn't it always...
-WM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply