April 14, 2009 at 1:41 pm
Management says do this, I say its a bad idea and they say do it anyway....
we have databases that we backup and restore as [dbname]Live to [dbname]Test then run some config scripts and such to make it not process credit cards and such.
As we have grown, managing these copies has become burdensome and management wants others in our organization to be able to do this and has required I write some code to get 'er done. I decided the only safe way (ostensibly safe) is to put all the code in an sp and create an intranet front end to allow the user to execute the sp. The problem is the code is dynamic sql and uses alter database statements as well as backup and restore. I tried the following:
EXECUTE AS Self (I have sysamdin permissions) I've also tried
EXECUTE AS [sql login] that has sysadmin permissions also.
but when calling the sp that resides in master using a sql login that has execute permission on this sp I get an error stating that the login specified in the execute as statement does not have permission to ALTER Database within this security context.
how can I get a low level user permission to execute this sp and only this sp?
'nix
April 29, 2009 at 6:00 am
You can create new sp to grant permission to this user, then run your SP finally revoke the permission for it, this may work
May 4, 2009 at 2:03 am
If I understand you correctly, you want to give the stored procedure permission for actions that the user isn't allowed to do. I had the same kind of challenge and found a very good article "Giving Permissions through Stored Procedures" by Erland Sommarskog http://www.sommarskog.se/grantperm.html (I used "Signing Procedures with Certificates")
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply