Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Role of a user in server

Status
Not open for further replies.

VenkatSQL

Technical User
Nov 6, 2007
14
IN
I need to find a role of a user using TSQL.


Like
In Enterprise Mananger -->Security -> Logins-> Right click to a user -> then properties ->Database access...

In that you can able to find that particular user's access right in server.

Is it possible to identify this using query....


Thanks

S.v
 
If this is for 2005 this will show you the user roles.

Code:
SELECT p.name, 
       s.name as Role, 
       create_date, 
       modify_date ,
       CASE WHEN is_disabled = 0
            THEN 'Enabled'
            ELSE 'Disabled'
        END as Status
 FROM sys.server_principals p
 JOIN (
select m.role_principal_id, 
       member_principal_id, 
       p.name
from sys.server_role_members m
JOIN sys.server_principals p
ON m.role_principal_id = p.principal_id
)  s
ON p.principal_id = s.member_principal_id
WHERE p.name not like '%#%'
  AND p.Type not in ('R')
Order by p.name, s.name

- Paul
- Experience is the name every one gives to their mistakes.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top