Listing the existing SQL Server Logins and Users

  • 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?

    When in doubt, turn on Profiler against Management Studio and see what it does.

  • 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 :)

  • 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.

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM