Solved

Query to display user and all permissions associated?

Posted on 2012-04-09
2
346 Views
Last Modified: 2012-04-17
Hello,

I am looking for a query that displays every user and his or hers associated permissions as well as associated server roles. Thank you in advance. . .
0
Comment
Question by:fstinc
2 Comments
 
LVL 7

Accepted Solution

by:
Anoo S Pillai earned 500 total points
ID: 37826072
I believe you are looking for two queries :) , Please see the following queries, which will be useful to you.

-- Object level permissions
SELECT      Obj.name ,Perm.permission_name , Principal.Name
FROM      sys.database_permissions      Perm
JOIN      sys.database_principals      Principal
ON      Perm.grantee_principal_id = Principal.principal_id
JOIN      (SELECT DISTINCT(id) ObjID FROM sys.syspermissions WHERE id > 0) AS SysPerm
ON      SysPerm.ObjID = Perm.major_id
JOIN      sys.sysobjects Obj
ON      Obj.id = SysPerm.objid
WHERE      Perm.major_id = SysPerm.ObjID

-- Role member ship

SELECT      ROLE.[name] Role ,  Principal.[name] Principal
FROM      sys.database_role_members ROLEMEMBER
JOIN      sys.database_principals as ROLE
ON            ROLEMEMBER.role_principal_id = ROLE.principal_id
JOIN      sys.database_principals as PRINCIPAL
ON            ROLEMEMBER.member_principal_id = PRINCIPAL.principal_id

Hope this is helpful.
0
 

Author Comment

by:fstinc
ID: 37858318
Thank you!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PERFORMANCE OF SQL QUERY 13 75
SQL Agent Timeout 5 64
Help with stripping out character in SQL LEFT/RIGHT/REPLACE 2 48
SQL Error - Query 6 41
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question