SP execute
-
I have a couple of DBs that have many user stored procedures. There is an application service account that has permission to run these. (granted directly at the sp) The developers are frequently dropping and creating these stored procedure. Every time they do, I have to go back into each one and grant Execute to the user.
Can I grant this user a role, besides system admin, that will allow it to run SPs as new ones are created?
Thanks.
-
You may run a script as a job that will check what stored procedures in sysobjects were created during last 24 hours, check if a permission is granted to your user by querying syspermissions with grantee = your user UID and if not execute Grant statement.
Yelena
Regards,Yelena Varsha
-
no but you can ask developer to exclusively grant execute permission to specified user whenever they create any new stored procedure via GRANT statement at the end.
if there are many such users, build application role with execute permission to all SPs and ask developer to grant execute permission to that role.
Hope this will help.
-
Below is a script you can schedule. It will grant execute permissions to all the procedures in your database to the user you specify. Create the procedure in master database and select in your job the required database. The default role name is Execute_storedProcedure and but you can pass in any other user or role name.
/*
This procedure grants Execute permissions to all procedures on a database to the role desired.
The default role is Execute_storedprocedure
*/
CREATE procedure sp_grant_permissions_proc @role varchar(100)='Execute_StoredProcedure' as
declare curname cursor for select name,user_name(uid) from sysobjects where xtype = 'P'
declare @procname varchar(100)
declare @username varchar(100)
open curname
fetch next from curname into @procname, @username
while @@fetch_status = 0
begin
declare @sql varchar(300)
set @sql = 'grant execute on ' + @username + '.[' + @procname + '] to ' + @role
exec(@sql)
fetch next from curname into @procname, @username
end
close curname
deallocate curname
-
Hello!
I think we are not seeing the forest because of all the trees
Why not ask your developers to NOT drop and recreate your SP's but ALTER them instead? In this way all permissions will be retained!Have a jolly time convincing your developers!
//Hanslindgren
-
I talked to the developers. They are going to add a Grant at the end.
Thanks everyone.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply