May 9, 2005 at 8:08 am
Need a suggestion for setting up a new Role. It will need to be able to INSERT, DELETE, UPDATE data and EXEC stored procs but should not be able to CREATE / DROP objects, etc.
Will granting DBDataReader and DBDataWriter to this role allow users in this role to exec SP's?
May 9, 2005 at 8:25 am
No, you will need to add EXEC rights for procedures explicitly.
May 9, 2005 at 8:39 am
You can create the procedure below in you master database and run this procedure from the database to which you want to grant permissions. Also, the default role name is Execute_StoredProcedure. You can pass in the role name as a parameter
/*
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
May 9, 2005 at 8:45 am
DECLARE @sProcName SysName
SET @sProcName=''
WHILE @sProcName IS NOT NULL
BEGIN
SELECT @sProcName =MIN(Name)
FROM SysObjects
WHERE Type='P' AND Name>@sProcName AND OBJECTPROPERTY(Id,'IsMSShipped')=0
IF @sProcName IS NOT NULL
EXEC ('GRANT EXEC ON ' + @sProcName + ' TO YourProcExecRole')
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply