Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How does a non-sysadmin restore a SQL database

Posted on 2014-10-21
5
Medium Priority
?
259 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

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

578 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