How does a non-sysadmin restore a SQL database

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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
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).
barnescoAuthor Commented:
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?
Anthony PerkinsCommented:
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.
barnescoAuthor Commented:
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?
Scott PletcherSenior DBACommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.