Sunday, April 30, 2017

SQL Server: How to get login IDs' Server Roles




Returns the names and id's of the roles and their members

SELECT sys.server_role_members.role_principal_id, role.name AS RoleName,   
    sys.server_role_members.member_principal_id, member.name AS MemberName  
FROM sys.server_role_members  
JOIN sys.server_principals AS role  
    ON sys.server_role_members.role_principal_id = role.principal_id  
JOIN sys.server_principals AS member  
    ON sys.server_role_members.member_principal_id = member.principal_id; 
 
 
 
 
 
 
Fixed server-level role

Description
sysadmin Members of the sysadmin fixed server role can perform any activity in the server.
serveradmin Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
securityadmin Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins.

** Security Note *\* The ability to grant access to the Database Engine and to configure user permissions allows the security admin to assign most server permissions. The securityadmin role should be treated as equivalent to the sysadmin role.
processadmin Members of the processadmin fixed server role can end processes that are running in an instance of SQL Server.
setupadmin Members of the setupadmin fixed server role can add and remove linked servers by using Transact-SQL statements. (sysadmin membership is needed when using Management Studio.)
bulkadmin Members of the bulkadmin fixed server role can run the BULK INSERT statement.
diskadmin The diskadmin fixed server role is used for managing disk files.
dbcreator Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
public Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on any object when you want the object to be available to all users. You cannot change membership in public.

Note: public is implemented differently than other roles. However, permissions can be granted, denied, or revoked from public.
 

No comments: