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?
Define "permission level". Object rights, database role membership, server role membership?
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:
Here are a few links that should help you also:
I hope this helps you.