SQL Grant Execute

Posted: 17-12-2008 | Author: flexicoder | Category: SQL Server, useful links

Found an article that had a nice SQL script to assign execute permissions to a user in SQL2000

Here is the script 

DECLARE @sql nvarchar(4000)

DECLARE @db  sysname ;
SET @db = DB_NAME()

DECLARE @u   sysname ;
SET @u = QUOTENAME('')

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'

EXEC master.dbo.xp_execresultset @sql,@db


Category: SQL Server, useful links by flexicoder

Leave a Reply