Please visit our sponsor
<![CDATA[UNKNOWN
--**************************************
-- 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
--
--Assumes:None
--
--Side Effects:None
--This code is copyrighted and has limited warranties.
--Please see http://www.Planet-Source-Code.com/xq/ASP/txtCodeId.277/lngWId.5/qx/vb/scripts/ShowCode.htm
--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
]]>