Solved

How does a non-sysadmin restore a SQL database

Posted on 2014-10-21
5
237 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:
Scott Pletcher 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

626 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