February 27, 2008 at 11:33 am
How do I give EXEC permission to all the store procs at once for a user on a database instead of going to properties of each store proc and giving permission.
If a user is assigned to db_owner role can he execute all the procs?
February 27, 2008 at 11:43 am
Spookily enough I'm working on a security script as I write.
DECLARE @Database sysname, @sql varchar (max)
SET @Database = db_name()
SET @sql = 'SELECT ''GRANT EXECUTE ON ''+ [Name] + '' TO myusername '''
SET @sql = @sql + 'FROM '+@Database+'.dbo.sysobjects '
SET @sql = @sql + 'WHERE xtype = ''P'''
SET @sql = @sql + 'AND [Name] NOT LIKE ''dt_% '''
SET @sql = @sql + 'ORDER BY [Name]'
EXEC (@SQL)
Gives you a script to apply...
February 27, 2008 at 12:45 pm
I use this cursor. I like it because I can change it for the type of permissions I want to grant based on the type of object
DECLARE @OBJNAME VARCHAR(256);
DECLARE OBJ_NAME CURSOR FOR SELECT NAME FROM sys.sysobjects WHERE xtype = 'p' AND NAME NOT LIKE 'dt%'
OPEN OBJ_NAME
FETCH OBJ_NAME INTO @OBJNAME
WHILE @@FETCH_STATUS = 0
BEGIN /* COMMENT OUT THE EXECUTE LINE AND UNCOMMENT OUT THE PRINT LINE TO MAKE SURE THAT YOU ARE NOT GRANTING UNINTENDED PERMSSIONS*/
EXECUTE ('GRANT EXECUTE ON ' +@OBJNAME+ ' TO USER')
-- PRINT ('GRANT EXECTUE ON ' +@OBJENAME+ ' TO USER')
FETCH NEXT FROM OBJ_NAME INTO @OBJNAME
END;
CLOSE OBJ_NAME
DEALLOCATE OBJ_NAME
Regards, Irish
February 27, 2008 at 9:04 pm
Well if all the procedures belong to one schema lets say dbo then you can give the execute permission on the schema. This way you will not have to worry for the exec permissions for any new proc that is added to that schema in future.
use [Pubs]
GO
GRANT EXECUTE ON SCHEMA::[dbo] TO [UserName]
GO
Yes users having db_owner can execute all the procs in that database.
February 28, 2008 at 8:33 am
CREATE PROC grants as
declare curse cursor for select name from sysobjects where type='P'
OPEN CURSE
declare @proc varchar(100)
declare @stmt nvarchar(200)
fetch next from curse into @proc
while @@fetch_status=0
begin
set @stmt='grant execute on '+@proc+' to bnbuser'
exec SP_EXECUTESQL @STMT
print @stmt
fetch next from curse into @proc
end
close curse
deallocate curse
GO
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply