patternsqlMinor
Restrict SQL Server 2008 R2 User to restore only one database (but not others)
Viewed 0 times
2008sqluserbutothersdatabaseoneserverrestrictnot
Problem
It is my understanding that a user has to be a "dbcreator" in order to restore databases. However, what if I want a specific user to restore only one specific database, but not gain access or restore any other database on the server? Doesn't "dbcreator" allow that user to essentially restore any database?
Solution
Log in as a server admin.
Create a stored procedure
Assign permission to execute it to the user.
As for where to put that stored procedure, I'm not sure it's possible to restore the DB from which you are running the SP, in which case you might want to create a completely blank database for the only purpose of storing this stored procedure.
Create a stored procedure
with execute as owner that restores a particular database.Assign permission to execute it to the user.
As for where to put that stored procedure, I'm not sure it's possible to restore the DB from which you are running the SP, in which case you might want to create a completely blank database for the only purpose of storing this stored procedure.
Context
StackExchange Database Administrators Q#12559, answer score: 3
Revisions (0)
No revisions yet.