Re: Restore database from within stored procedure
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Fri, 14 Sep 2007 22:04:48 +0000 (UTC)
Franky (Franky@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
My intention is to create a stored procedure with basically 2
paramenters to allow developers to do a restore without having the
necesary rights on the sql-server. First parameter = path to the
backupfile Second parameter = name from db to restore. My code to
retrieve data from a backupfile (datasets, datafiles) and the generation
of the restore command works fine.
But as I mentioned this procedure is made to allow developers to restore
databases without having the right to restore databases.
So I thaught I could just make a stored procedure with the 'execute as
AccountWithLotsOfPermissions' option and give the dev-group permissions to
execute that stored procedure.
But I get the message (even when I execute that stored procedure with an
account which has all permissions)
Msg 262, Level 14, State 1, Line 1
CREATE DATABASE permission denied in database 'master'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
This is because when you do EXECUTE AS you are sandboxed into the
current database, unless the database is marked as trustworthy. Which
may have security implications. Or be perfectly harmless.
In any case, my preference is to use certificate signing instead.
I have an article on my web site that discusses both certificate
signing and EXECUTE AS in detail, and the article includes example
for a similar problem (giving bulk copy permissions). See
http://www.sommarskog.se/grantperm.html.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Prev by Date: Re: Regular users rying to run sp_start_job
- Next by Date: Re: Query/View Privileges
- Previous by thread: Re: "Account not associated with a trusted SQL Server connection"
- Next by thread: SQL 2005, sysobjects and security
- Index(es):
Relevant Pages
|
|
