I use this SP. I plan to modify it to allow me to pass the username or
Role as a variable, but have not had the time. I would give credit to
who created it, but I don't recall. I create it in Master so I can use
it on each DB.

Sorry about the formatting, I am not sure how to stop the double spacing
of my posts.

Stephen



Create PROCEDURE [dbo].[sp_Permissionsforuser]
AS
SET nocount ON
IF EXISTS (SELECT *
FROM tempdb..sysobjects
WHERE [name] LIKE '#perm%')
DROP TABLE #perm
CREATE TABLE #perm (
[qid] INT IDENTITY( 1 , 1 ) NOT NULL,
[user] INT NOT NULL,
[inherfrom] INT NOT NULL,
PRIMARY KEY( [user] , [inherfrom] ))
INSERT INTO #perm
([user],
[inherfrom])
SELECT [uid],
[uid]
FROM sysusers
WHERE issqlrole = 0
AND hasdbaccess = 1
AND uid <> 1
WHILE EXISTS (SELECT p1.[user],
groupuid
FROM sysmembers
INNER JOIN #perm p1
ON p1.[inherfrom] = sysmembers.memberuid
WHERE groupuid NOT IN (SELECT inherfrom
FROM #perm p2
WHERE p2.[user] = p1.[user]))
BEGIN
INSERT INTO #perm
([user],
[inherfrom])
SELECT p1.[user],
groupuid
FROM sysmembers
INNER JOIN #perm p1
ON p1.[inherfrom] = sysmembers.memberuid
WHERE groupuid NOT IN (SELECT inherfrom
FROM #perm p2
WHERE p2.[user] = p1.[user])
END
SELECT u2.[name] AS username,
u1.[name] AS inheritesvia,
CASE xtype
WHEN 'U' THEN 'Table'
WHEN 'V' THEN 'View'
WHEN 'S' THEN 'System'
WHEN 'P' THEN 'Procedure'
WHEN 'FN' THEN 'Function'
END AS objecttype,
sysobjects.[name] AS OBJECT,
CASE
WHEN xtype IN ('U','V','S') THEN CASE
WHEN (actadd & 1) = 1
THEN 'Granted'
WHEN (actmod & 1) = 1
THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [select],
CASE
WHEN xtype IN ('U','V','S') THEN CASE
WHEN (actadd & 8) = 8
THEN 'Granted'
WHEN (actmod & 8) = 8
THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [insert],
CASE
WHEN xtype IN ('U','V','S') THEN CASE
WHEN (actadd & 2) = 2
THEN 'Granted'
WHEN (actmod & 2) = 2
THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [update],
CASE
WHEN xtype IN ('U','V','S') THEN CASE
WHEN (actadd & 16) = 16
THEN 'Granted'
WHEN (actmod & 16) = 16
THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [delete],
CASE
WHEN xtype IN ('P','FN') THEN CASE
WHEN (actadd & 32) = 32
THEN 'Granted'
WHEN (actmod & 32) = 32
THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [exec],
CASE
WHEN xtype IN ('U','V','S') THEN CASE
WHEN (actadd & 4) = 4
THEN 'Granted'
WHEN (actmod & 4) = 4
THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [dri]
FROM syspermissions
INNER JOIN #perm
INNER JOIN sysusers u1
ON u1.uid = [inherfrom]
INNER JOIN sysusers u2
ON u2.uid = [user]
ON [inherfrom] = grantee
INNER JOIN sysobjects
ON sysobjects.[id] = syspermissions.[id]
WHERE xtype = 'u'
AND u2.name IN ('datachange') --Add name(s) in this to get a
specific name
-- AND u1.name IN ('accountingrole') --Put role name here to
see all users in the role and permissions
ORDER BY [inheritesvia]
--[UserName]
--, [ObjectType]
--, [Object]
DROP TABLE #perm

-----Original Message-----
From: Kackos Emil
[mailto:mssqldba-ezmlmshield-x39174441.[Email address protected]
Sent: Friday, November 30, 2007 10:44 AM
To: LazyDBA Discussion
Subject: Script to list Server and Database Roles for Selected users

Does anyone have a script that lists all the server roles and all the
database roles in each database for selected users?

I need to easily check all roles for 6 users on a number of servers.

Thanks

Emil





---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


-----------------------------------------
The information contained in this e-mail message is intended only
for the personal and confidential use of the recipient(s) named
above. This message may be an attorney-client communication and/or
work product and as such is privileged and confidential. If the
reader of this message is not the intended recipient or an agent
responsible for delivering it to the intended recipient, you are
hereby notified that you have received this document in error and
that any review, dissemination, distribution, or copying of this
message is strictly prohibited. If you have received this
communication in error, please notify us immediately by e-mail, and
delete the original message.

MS Sql Server LazyDBA home page