Solved

How does a non-sysadmin restore a SQL database

Posted on 2014-10-21
5
206 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 69

Accepted Solution

by:
ScottPletcher earned 500 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
using t-sql EXISTS 8 23
Mssql SQL query 14 28
SQL Server 2008 to SQL Server 2016 Replication 8 24
login and database user 3 22
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now