Solved

Query to display user and all permissions associated?

Posted on 2012-04-09
2
350 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

623 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