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.

  • Define "permission level". Object rights, database role membership, server role membership?
    – mrdenny
    Commented Aug 21, 2012 at 1:53
  • When in doubt, turn on Profiler against Management Studio and see what it does.
    – Jon Seigel
    Commented Aug 21, 2012 at 2:54

2 Answers 2


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

;with ServerPermsAndRoles as
        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,
    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

        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.

  • This was very useful! I added a string-concatenation to "show me a command to grant/revoke the permissions to each login listed", like so -- CAST([srvperm].[state_desc] AS VARCHAR(MAX)) + ' ' + CAST(srvperm.[permission_name] AS VARCHAR(MAX)) + ' TO [' + CAST(srvprin.name AS VARCHAR(MAX)) + '];' as GrantQuery which helps to replicate those permissions if you're migrating a server :)
    – NateJ
    Commented Mar 3, 2016 at 1:10

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:


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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