Solved

Query to display user and all permissions associated?

Posted on 2012-04-09
2
348 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Isolation level in SQL server 3 58
Email Notifications for SQL 2005 9 43
Database maintenance 36 141
Change this SQL to get all nodes 3 56
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

759 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