Stored Procedures in SQL Server -- Permissions

This is a small code that displays stored procedures/functions for which the user does or does not have permissions to execute. In the event, the user has no permissions, also display the grant SQL neccesary to give them EXECUTE permissions.


*/ /* Set this to your desired target database */

USE DATABASE_NAME
DECLARE @TARGETUSER varchar(255), @SHOWPERMS bit SELECT

/* Set this to your desired target user */
@TARGETUSER = 'USER NAME HERE',

/* Set this to 0 (zero) to show objects the user has NOpermissions upon */

/* Set this to 1 (one) to show objects the user HAS permissionsupon */

@SHOWPERMS = 0

IF ( @SHOWPERMS) = 0
BEGIN PRINT 'showing no permissions'
SELECT name, id FROM sysobjects
WHERE
xtype IN ( 'P', 'TF' )
AND base_schema_ver < 16
AND id NOT IN
( SELECT SO.id FROM syspermissions SP
LEFT JOIN sysusers SU ON ( SP.grantee = SU.uid )
LEFT JOIN sysobjects SO ON ( SP.id = SO.id )
WHERE ( UPPER(SU.name) = UPPER (@TARGETUSER)
) )
print 'GRANT EXECUTE statements ... '

SELECT 'GRANT EXECUTE ON [' + name + '] TO [' + @TARGETUSER + ']'
FROM sysobjects
WHERE
xtype IN ( 'P', 'TF' )
AND base_schema_ver < 16
AND id NOT IN ( SELECT SO.id FROM syspermissions SP
LEFT JOIN sysusers SU ON ( SP.grantee =SU.uid )
LEFT JOIN sysobjects SO ON ( SP.id =SO.id )
WHERE ( UPPER(SU.name) = UPPER(@TARGETUSER) ) )
END ELSE
BEGIN PRINT 'showing no permissions'

SELECT SU.name, SO.name, SO.xtype FROM syspermissions SP
LEFT JOIN sysusers SU ON ( SP.grantee = SU.uid )
LEFT JOIN sysobjects SO ON ( SP.id = SO.id )
WHERE ( UPPER( SU.name ) = UPPER ( @TARGETUSER )
AND SO.xtype IN ( 'P', 'TF' ) )
ORDER BY SU.name

END

[/code]