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.
Syntax to create User-defined server Role:
CREATE SERVER ROLE Role-Name;
Adding Domain account to server Role:
ALTER SERVER ROLE Role-Name ADD MEMBER [Domain\AccountName]
Adding a SQL Server login to a server role:
ALTER SERVER ROLE Role-Name ADD MEMBER Login-Name
Syntax to create User-defined server Role:
CREATE SERVER ROLE Role-Name;
Adding Domain account to server Role:
ALTER SERVER ROLE Role-Name ADD MEMBER [Domain\AccountName]
Adding a SQL Server login to a server role:
ALTER SERVER ROLE Role-Name ADD MEMBER Login-Name
Granting specific permission to user-defined Server Role:
Grant view Server state to Role-Name
Grant view any database to Role-Name
Removing Domain account from server Role:
ALTER SERVER ROLE Role-Name DROP MEMBER [Domain\Window-User]
Removing a SQL Server login to a server role:
ALTER SERVER ROLE Role-Name DROP MEMBER Login-Name
Changing the name of user-defined server role
ALTER SERVER ROLE Role-Name WITH NAME = New-Name;
Dropping a user-defined Role:
Syntax :
Drop Server Role Role-Name.
Remarks:User-defined server roles that own securables cannot be dropped from the server. To drop a user-defined server role that owns securables, you must first transfer ownership of those securables or delete them.
User-defined server roles that have members cannot be dropped. To drop a user-defined server role that has members, you must first remove members of the role by using ALTER SERVER ROLE.
0 comments: