Print Application Role Object List?

[smedvid]

Is there a SP that will generate a list of Application Roles that includes a list of Objects that have Select, Update, Delete, Execute, etc. permissions? I need to perform an audit of a couple of roles on our SQL Server Database and each role has hundreds of objects and it is difficult to do online. tia,

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[smedvid]

Found a SP that appears to work.... Sharing the wealth of knowledge...

http://www.sqlteam.com/item.asp?ItemID=671

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


/****** Stored Procedure dbo.sp_showpermissions v 1.0 ******/

/****** Created 07/23/1998 by Brent Huscher ******/

/****** Modified 07/23/1998 by Brent Huscher ******/

CREATE PROCEDURE sp_showpermissions

@group VARCHAR(30) = NULL

AS

SET NOCOUNT ON

IF @group is NULL SELECT @group = 'public'

IF EXISTS (SELECT name from sysusers where name = @group and uid = gid)

BEGIN

SET NOCOUNT OFF

SELECT "ROLE NAME" = left(b.name, 30),
"OBJECT NAME" = left(c.name, 30),
"ACTION" = CASE a.action
WHEN 26 THEN ' 26 REFERENCES'
WHEN 193 THEN '193 SELECT'
WHEN 195 THEN '195 INSERT'
WHEN 196 THEN '196 DELETE'
WHEN 197 THEN '197 UPDATE'
WHEN 198 THEN '198 CREATE TABLE'
WHEN 203 THEN '203 CREATE DATABASE'
WHEN 204 THEN '204 GRANT_W_GRANT'
WHEN 205 THEN '205 GRANT'
WHEN 206 THEN '206 REVOKE'
WHEN 207 THEN '207 CREATE VIEW'
WHEN 222 THEN '222 CREATE PROCEDURE'
WHEN 224 THEN '224 EXECUTE'
WHEN 228 THEN '228 DUMP DATABASE'
WHEN 233 THEN '233 CREATE DEFAULT'
WHEN 235 THEN '235 DUMP TRANSACTION'
WHEN 236 THEN '236 CREATE RULE'
END,
"TYPE" = CASE c.type
WHEN 'C' THEN 'C CHECK constraint'
WHEN 'D' THEN 'D Default or DEFAULT constraint'
WHEN 'F' THEN 'F FOREIGN KEY constraint'
WHEN 'K' THEN 'K PRIMARY KEY or UNIQUE constraint'
WHEN 'L' THEN 'L Log'
WHEN 'P' THEN 'P Stored procedure'
WHEN 'R' THEN 'R Rule'
WHEN 'RF' THEN 'RF Stored procedure for replication'
WHEN 'S' THEN 'S System table'
WHEN 'TR' THEN 'TR Trigger'
WHEN 'U' THEN 'U User table'
WHEN 'V' THEN 'V View'
WHEN 'X' THEN 'X Extended stored procedure'
END
FROM sysprotects a, sysusers b, sysobjects c
WHERE a.uid = b.uid
AND c.id = a.id
AND b.name = @group
ORDER BY b.name, c.name, a.action

END

ELSE

BEGIN
PRINT 'You did not provide a valid role name'
PRINT ''
SELECT 'Valid role names for the ' + db_name() + ' database are:'
PRINT ''
SELECT "Role Name"=name FROM sysusers WHERE uid = gid ORDER BY name
PRINT ''
PRINT ''
PRINT 'Syntax: sp_showpermissions [role_name]'
PRINT ''
PRINT 'sp_showpermissions with no parameter will show permissions for public'
END

SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...