HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Restrict SQL Server 2008 R2 User to restore only one database (but not others)

Submitted by: @import:stackexchange-dba··
0
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 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.