Role of a user in server

[VenkatSQL]

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

 

[ptheriault]

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

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.