spObjectPermissions

				
--**************************************
-- Name: spObjectPermissions
-- Description:Stored Procedure to query object permissions for specified user
-- By: John C Kirwin
--
-- Inputs:User's name = @UserID AS VARCHAR(30)
--
-- Returns:User, Grant Status, Object Permission, Database Object
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=277&lngWId=5--for details.--**************************************

IF EXISTS (SELECT * FROM dbo.sysobjects 
 WHERE id = object_id(N'[dbo].[spObjectPermissions]') 
 AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[spObjectPermissions]
GO
CREATE PROCEDURE [spObjectPermissions] @UserID AS VARCHAR(30) 
AS
/***********************************************************/
/* Stored Procedure:spObjectPermissions*/
/* Creation Date:02/12/2001*/
/* Written by: John C. Kirwin */
/* */
/* Purpose:Stored Procedure to query object */
/*permissions for specified user */
/* */
/* Input Parameters:@UserID */
/* */
/* Updates:*/
/* Date Author Purpose */
/* <this section is used to track changes to the script> */
/* */
/* Examples:EXEC spObjectPermissions 'public' */
/* */
/***********************************************************/
SELECT sysusers.name
, Status = 
CASE protecttype 
	 WHEN 204 THEN 'GRANT_W_GRANT'
	 WHEN 205 THEN 'GRANT'
	 WHEN 206 THEN 'REVOKE'
 ELSE 'Unknown'
END
, Permission = 
CASE action
	 WHEN 193 THEN 'SELECT'
	 WHEN 195 THEN 'INSERT'
	 WHEN 196 THEN 'DELETE'
	 WHEN 197 THEN 'UPDATE'
	 WHEN 26 THEN 'REFERENCE'
	 WHEN 224 THEN 'EXECUTE'
 ELSE 'Unknown'
END 
, sysobjects.name 
FROM sysprotects, sysobjects, sysusers
WHERE sysobjects.id = sysprotects.id 
AND sysprotects.action in (193, 195, 196, 197, 224, 26) 
AND sysprotects.uid = sysusers.uid
AND sysusers.name = @userID
ORDER BY sysusers.name, sysobjects.xtype, sysobjects.name
GO