Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How does a non-sysadmin restore a SQL database

Posted on 2014-10-21
5
Medium Priority
?
254 Views
Last Modified: 2014-12-08
I've granted a non-sysadmin user the following:

DBCreator role
View server state
View any definition
View any database
DBO of a destination database
db_owner of destination database

He attempts to restore a database where he's not a DBO, and it appears to have restored, but he's denied viewing it (The database is not accessible).

How can a non-sysadmin restore a database that's not his and see it. This is useful if he wants to restore a prod database to test where the prod db has 'sa' ownership.

Thanks
0
Comment
Question by:barnesco
  • 2
  • 2
5 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40396032
In order to view the database they will have to be given access to the database by creating a user in that database and give them access.  Whoever is going to do the CREATE and GRANT will need the appropriate permissions (ALTER ANY USER).
0
 

Author Comment

by:barnesco
ID: 40396132
I see what you mean, but I can't grant a user ALTER ANY USER permissions since the database would be restored from prod. Is there any conceivable method or way for a user to grant himself db_creator permissions through a proc with executable rights?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40396796
but I can't grant a user ALTER ANY USER permissions since the database would be restored from prod.
I was not suggesting that:  I was stating that who ever is going to do the CREATE USER and GRANT will need the appropriate permissions, not the user themselves.  In other words, assuming you are the sysadmin, once the database is created you will have to CREATE the user and GRANT the appropriate permissions to the user in that database.
0
 

Author Comment

by:barnesco
ID: 40397120
I understand a user with those rights (as a sysadmin) has CREATE USER and GRANT permissions, and I know I could assign to someone else. But there are no other users to grant those permissions to (other than myself), and I want a non-sysadmin to be able to restore a database from prod.

How can a non-sysadmin restore a prod database to test, without assistance from another resource or by granting anyone CREATE USER and GRANT permissions, and be able to access the database that he restored?
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40397285
I'd create a stored procedure to do this, and put it in the master db (so that it can be invoked from any db).

The stored proc would run under a special user with full permissions.  Then GRANT that user the right to run the stored proc.  I'd do that rather than give them dbcreator authority.  This gives you flexibility, as you could add checks in the stored proc to prevent certain dbs from being restored (msdb, etc .).

The stored proc would also do any needed resync'ing of users to logins and other "clean up" done when restoring a db to a different server.

If you're willing to go that route and want an example of such a proc, just let me know.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question