Contents
When does a SQL Server login belong to a public role?
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.
How to view sys.server _ role members ( Transact-SQL )?
Logins can view their own server role membership and can view the principal_id’s of the members of the fixed server roles. To view all server role membership requires the VIEW DEFINITION ON SERVER ROLE permission or membership in the securityadmin fixed server role. For more information, see Metadata Visibility Configuration.
How to add or remove a server role in SQL?
To add or remove server role membership, use the ALTER SERVER ROLE (Transact-SQL) statement. Logins can view their own server role membership and can view the principal_id’s of the members of the fixed server roles.
Can a Windows account be added to a SQL Server role?
You can add server-level principals ( SQL Server logins, Windows accounts, and Windows groups) into server-level roles. Each member of a fixed server role can add other logins to that same role. Members of user-defined server roles cannot add other server principals to the role.
How to alter a user defined server role?
User-defined server roles. To add a member to a user-defined server role, you must be a member of the sysadmin fixed server role or have CONTROL SERVER or ALTER ANY SERVER ROLE permission. Or you must have ALTER permission on that role.
Can a Windows account be added to a server level role?
You can add server-level principals ( SQL Server logins, Windows accounts, and Windows groups) into server-level roles. Each member of a fixed server role can add other logins to that same role. Members of user-defined server roles cannot add other server principals to the role. Note.
How to change the owner of a server role?
You can view server roles by querying the sys.server_role_members and sys.server_principals catalog views. To change the owner of a user-defined server role, use ALTER AUTHORIZATION (Transact-SQL). Requires ALTER ANY SERVER ROLE permission on the server to change the name of a user-defined server role.