Create stored procedure with Execute As

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Create stored procedure with Execute As

Author

Topic

Gawaine79
Starting Member

6 Posts

Posted - 2006-07-14 : 14:16:43

Hi I have made a store procedure that can recreate a single database and update it to the most current information. However the users that utilize this database do not haver permissions to restore databases.

so i created a database login let's call it Billy with the authority to create databases...created/recreated a database check.

Then i created a stored procedure with the following

create procedure restoreDB
with execute as 'Billy'
as
BEGIN

restore database ...

END

now I give the user who has lower privilages let's call the login bob, access to execute this stored procedure.

however when i run the procedure as bob i get the error: User does not have permission to RESTORE database

Am i doing something wrong or is it just not possible to do?

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-14 : 19:52:33

not possible.
Get the sp to put the request into a table and have a scheduled job execute it.
Or you could use openquery or osql to get another connection with the correct permissions.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jocampo
Starting Member

48 Posts

Posted - 2006-07-17 : 09:12:39

Did you already try adding this user to the DB Backup Operator Role? Doin' this he will have all the required permissions to backup and restore your databases ... or ... this is not what you want?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-17 : 09:22:59

membership to db_owner fixed role does not grant permission to restore

bob must be the owner of the database to do this, otherwise add the login to dbcreator (highest would be sysadmin)

--------------------
keeping it simple...

Gawaine79
Starting Member

6 Posts

Posted - 2006-07-17 : 14:07:20

yea i do not want bob to have restore permissions but i want to allow him to be able to restore this one database via the stored procedure.

I will work with NR's suggestion for now thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources