Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I know we can check the logins and the users that are defined, using GUI in SQL Server, but am wondering how we can do this check using script.

I ran the query below but it shows Principal_id which I'm not sure how to map to get the permission level.

SELECT * FROM Sys.login_token 

So is there any built-in stored proc that can list the logins and the users with their permission level?

Thank you.

share|improve this question
    
Define "permission level". Object rights, database role membership, server role membership? –  mrdenny Aug 21 '12 at 1:53
    
When in doubt, turn on Profiler against Management Studio and see what it does. –  Jon Seigel Aug 21 '12 at 2:54
add comment

2 Answers

up vote 4 down vote accepted

Not sure if there's a built-in way, but give this query a try for server principals:

;with ServerPermsAndRoles as
(
    select
        spr.name as principal_name,
        spr.type_desc as principal_type,
        spm.permission_name collate SQL_Latin1_General_CP1_CI_AS as security_entity,
        'permission' as security_type,
        spm.state_desc
    from sys.server_principals spr
    inner join sys.server_permissions spm
    on spr.principal_id = spm.grantee_principal_id
    where spr.type in ('s', 'u')

    union all

    select
        sp.name as principal_name,
        sp.type_desc as principal_type,
        spr.name as security_entity,
        'role membership' as security_type,
        null as state_desc
    from sys.server_principals sp
    inner join sys.server_role_members srm
    on sp.principal_id = srm.member_principal_id
    inner join sys.server_principals spr
    on srm.role_principal_id = spr.principal_id
    where sp.type in ('s', 'u')
)
select *
from ServerPermsAndRoles
order by principal_name

Basically what it does it gets the permissions granted and denied, and unions it with the role membership. It should give you a brief look on the security for server logins. Let me know if that's what you're looking for.

share|improve this answer
add comment

Try this one - this will list users, objects and the permissions that they have on those objects:

SELECT p.name, o.name, d.*
FROM sys.database_principals AS p
JOIN sys.database_permissions AS d ON d.grantee_principal_id = p.principal_id
JOIN sys.objects AS o ON o.object_id = d.major_id

You should also check out the sys.fn_my_permissions function:

http://msdn.microsoft.com/en-us/library/ms188367.aspx

Here are a few links that should help you also:

PERMISSIONS: http://msdn.microsoft.com/en-us/library/ms191291.aspx

SYS.DATABASE_PERMISSIONS: http://msdn.microsoft.com/en-us/library/ms188367.aspx

I hope this helps you.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.