DECLARE procs CURSOR FOR SELECT [name]
FROM sys.objects
WHERE type IN('p','AF','FN')
DECLARE @name AS VARCHAR(250)
DECLARE @stmt AS VARCHAR(1000)
OPEN procs
FETCH NEXT FROM procs INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
SET @stmt='GRANT EXECUTE ON '+@name+' TO [USERNAME]'
EXEC(@stmt)
PRINT @stmt
FETCH NEXT FROM procs INTO @name
END
CLOSE procs
DEALLOCATE procs
Tuesday, November 26, 2013
Grant Execute to all stored procs in a database
This script gives execute rights to all stored procs for the [USERNAME]. It uses a cursor, but it works well
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment