Listing the existing SQL Server Logins and Users

Question

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, Aug 21 '12 at 01:53
When in doubt, turn on Profiler against Management Studio and see what it does. — Jon Seigel, Aug 21 '12 at 02:54

score 15 · Accepted Answer · answered Aug 21 '12 at 02:43

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.

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, Mar 03 '16 at 01:10

score 7 · Answer 2 · answered Aug 21 '12 at 06:37

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.

Listing the existing SQL Server Logins and Users

2 Answers2

Linked