| |
use master
go
create procedure sp_grantexec(@user sysname,@pattern sysname = NULL,@debug int = 0)
as
set nocount on
declare @ret int
declare @sql nvarchar(4000)
declare @db sysname ; set @db = DB_NAME()
declare @u sysname ; set @u = QUOTENAME(@user)
set @sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @u + ''' FROM INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
if @pattern is not null
set @sql = @sql + N' AND ROUTINE_NAME LIKE ''' + @pattern + ''''
if @debug = 1 print @sql
else
exec @ret = master.dbo.xp_execresultset @sql,@db
If @ret <> 0
begin
raiserror('Error executing command %s',16,1,@sql)
return -1
end
|
|
|
articles