Server roles set server-wide administrator privileges for SQL Server logins. You can manage server roles by role or by individual logins.
To assign or change server roles for a login, follow these steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server, and then work your way down to the Security folder.
Expand the Security folder and the Logins folder to list the current logins. Right-click a login, and then select Properties to display the SQL Server Login Properties dialog box and view the properties of the login.
Select the Server Roles page, as shown in Figure 8-3.
Grant server roles by selecting the check boxes next to the roles you want to use. You learned about server roles in the section titled "Server Roles" earlier in this chapter.
When you have finished configuring server roles, click OK.
You can also configure server roles with Transact-SQL. The sp_addsrvrolemember stored procedure adds a login to a server role, and you can use it as shown in Example 8-9.
Note
To use sp_addsrvrolemember or sp_dropsrvrolemember, you must have ALTER ANY LOGIN permission on the server and membership in the role to which you are adding the new member.
Example 8-9. sp_addsrvrolemember Syntax and Usage
Syntax
sp_addsrvrolemember [@loginame =] 'login', [@rolename =] 'role'
Usage
EXEC sp_addsrvrolemember 'GALAXYWRSTANEK', 'sysadmin'
The sp_dropsrvrolemember stored procedure removes a login from a role, and you can use it as shown in Example 8-10.
The easiest way to assign roles to multiple logins is to use the Server Roles Properties dialog box. To access this dialog box and configure multiple logins, follow these steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server, and then work your way down to the Security folder.
Expand the Server Roles node, and then right-click the role you want to configure. This opens the Server Role Properties dialog box shown in Figure 8-4.
To add logins, click Add, and then use the Select Logins dialog box to select the logins to add. You can enter partial names, and then click Check Names to expand the name. To search for names, click Browse.
To remove a login, select it, and then click Remove.
When you have finished configuring server roles, click OK.
To revoke access rights or to remove a user from a role in a database, complete the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server.
Expand the server’s Security folder, and then expand the related Logins folder.
Double-click the login that you want to configure to display the Login Properties dialog box.
Select the Server Roles page. Clear the check box next to the server roles that you want to remove from this login.
Select the Database Access page. Clear the check box next to the databases to which this user should not have access. Alternately, select a database to which access is permitted, and then modify the granted roles by clearing options under Database Roles For...
When you have finished, click OK.