SQL Server 2000 Security

Updated : July 30, 2001

Both inside and outside organizations, Microsoft SQL Server 2000 is being used more and more. Whether employees, contractors, or outside users access your databases, your job as an administrator is to manage that database access efficiently. You do this by creating user logins, configuring login permissions, and assigning roles. The permissions and roles you assign determine which actions users can perform, as well as what kinds of data they can access.

Your primary goals in managing security should be to

  • Balance the user's need for access to data against your need for protection from unauthorized access to data

  • Restrict database permissions so that users are less likely to execute harmful commands and procedures (whether maliciously or accidentally)

  • Close off other security holes, such as those that may be caused by ordinary users being members of the Windows NT Administrators group

Note: If you're working with the Personal Edition of SQL Server, the Microsoft Windows 95 or Windows 98 system hosting SQL Server can only use SQL Server logins. Thus, Windows authentication, domain user's accounts, and domain group accounts aren't available.

On This Page

SQL Server 2000 Security
Managing Server Logins
Configuring Server Roles
Controlling Database Access and Administration
Managing Database Permissions

SQL Server 2000 Security

You control access to databases with the components of the SQL Server security model. These components include

  • SQL Server authentication modes

  • Server logins

  • Permissions

  • Roles

SQL Server 2000 Authentication Modes

The SQL Server security model has two authentication modes:

  • Windows Authentication only Works best when the database is accessed within the organization only

  • Mixed security Works best when outside users may access the database or when you don't use Windows domains

You configure these security modes on the server level, and they apply to all databases on the server. Keep in mind that each database server instance has a separate security architecture. This means different database server instances can have different security modes.

Windows Authentication

With Windows Authentication, you can use the user and group accounts available in the Windows domain for authentication. This lets domain users access databases without your having to provide a separate SQL Server login ID and password. The benefits of this are that domain users don't have to keep track of multiple passwords, and if they update their domain password, they don't have to change SQL Server passwords as well. However, users are still subject to all the rules of the Windows security model, and you can use this model to lock accounts, audit logins, and force users to change their passwords periodically.

When you use Windows Authentication, SQL Server automatically authenticates users based on their user account names or their group membership. If you've granted the user or the user's group access to a database, the user is automatically granted access to that database. By default, two local accounts are configured to use SQL Server. These accounts are the local Administrators group account and the local Administrator user account. (I include Administrator because it's a member of the Administrators group by default in Windows NT/2000.) Local accounts are displayed as BUILTIN\<AccountName> or COMPUTERNAME\ <AccountName> in Enterprise Manager. For example, Administrators is displayed as BUILTIN\Administrators.

Real World Domain accounts are the best way to manage users who access the database from within the organization. Also, if you assign users to domain groups and then configure access for these groups in SQL Server, you cut down on the amount of administration you have to do. For example, if you assign users in the marketing department to a marketing group and then configure this group in SQL Server, you have only one account to manage instead of 10, 20, 50, or more. When employees leave the organization or change departments, you don't have to delete user accounts. When new employees are hired, you don't have to create new accounts either—you just make sure that they're added to the correct group in Windows NT/2000.

Mixed Security and SQL Server Logins

With mixed security, you use both Windows Authentication and SQL Server logins. SQL Server logins are primarily used by users outside the company, such as those who might access the database from the Internet. You can configure applications that access SQL Server from the Internet to use specific accounts automatically or to prompt the user for a SQL Server login ID and password.

With mixed security, SQL Server first determines whether the user is connecting using a valid SQL Server login. If the user has a valid login and has the proper password, the user connection is accepted. If the user has a valid login but has an improper password, the user connection is refused. SQL Server checks the Windows NT/2000 account information only if the user doesn't have a valid login. Here, SQL Server determines whether the Windows NT/2000 account has permission to connect to the server. If the account has permission, the connection is accepted. Otherwise, the connection is refused.

All SQL Server databases have built-in SQL Server logins with special purposes. These logins are sa, guest, and dbo, and they're discussed next under "Server Logins."

Server Logins

You configure access to SQL Server using server logins or the roles to which those logins belong, or both. Just as there are two authentication modes, there are also two kinds of server logins. You create domain logins using domain accounts, which can be domain or local user accounts, local group accounts, or universal and global domain group accounts. You create SQL Server logins by specifying a unique login ID and password. Several logins are configured by default, and these include

  • The local Administrators group

  • The local Administrator account

  • The sa login

  • The guest login (a special login configured but not automatically enabled)

  • The dbo user (a special database user)

The sections that follow examine these logins.

Working with Administrators

Administrators is a local group on the database server. This group's members normally include the local Administrator user account and any other users set to administer the system locally. In SQL Server this group is granted the System Administrators (sysadmin) server role by default.

Note: In Enterprise Manager, server roles are identified by full names, such as System Administrators, and regular names, such as sysadmin. When you reference server roles in procedures or Transact-SQL, you'll use the regular name, such as sysadmin. Unfortunately, SQL Server 2000 doesn't have full names for database roles, which isn't consistent with the precedent set for server roles.

Working with Administrator

Administrator is a local user account on the server. This account provides administrator privileges on the local system and you use it primarily when you install a system. If the host computer is part of a Windows domain, the Administrator account usually has domain-wide privileges as well. In SQL Server this account is granted the System Administrators server role by default.

Working with the sa Login

The sa login is the system administrator's account for SQL Server. With the new integrated and expanded security model, sa is no longer needed and is primarily provided for backward compatibility with previous SQL Server versions. As with other administrator logins, sa is granted the System Administrators server role by default. When you install SQL Server, the sa login isn't assigned a password.

To prevent unauthorized access to the server, you should set a password for this account.

Best Practice Because the sa login is widely known to malicious users, you may want to delete or disable this account whenever possible. Instead, make System Administrators members of the System Administrators server role and have them log on using their own logins. Anyone with the System Administrators server role can then log in and administer the server. If you ever get locked out of the server, you can log on to the server locally using an account with local administrator privileges and then reset passwords or assign privileges as necessary.

Working with the Guest Login

The guest login is a special login that you can add to a database to allow anyone with a valid SQL Server login to access the database. Users who access a database by way of the guest account assume the identity of the guest user and inherit all the privileges and permissions of the guest account. For example, if you configure the domain account GOTEAM to access SQL Server, GOTEAM can access any database with a guest login, and when GOTEAM does, that person is granted all the permissions of the guest account.

By default, the guest login doesn't exist in newly created databases, but you can add it or delete it from all databases except master and tempdb. Most users access master and tempdb as guests and because of this, you can't remove the guest account from these databases. Don't worry—a guest has limited permissions and privileges in master and tempdb.

Before using the guest login, you should note the following:

  • The guest login is a member of the public server role and inherits the permissions of this role.

  • You must assign the guest account to a database before anyone can access it as a guest.

  • The guest login is used only when a user account has access to SQL Server but doesn't have access to the database through this user account.

Working with the dbo User

The database owner, or dbo, is a special type of database user and is granted special privileges. Generally speaking, the user who created a database is the database owner. The dbo is implicitly granted all permissions on the database and can grant these permissions to other users. Because members of the System Administrators server role are mapped automatically to the special user dbo, logins with the System Administrators role can perform any tasks that a dbo can.

Objects created in SQL Server databases also have owners. These owners are referred to as the database object owners. Objects created by a member of the System Administrators server role belong to the dbo user automatically. Objects created by users who aren't members of the System Administrators server role belong to the user creating the object and must be qualified with the name of that user when other users reference them. For example, if GOTEAM is a member of the System Administrators server role and creates a table called Sales, Sales belongs to dbo and is qualified as dbo.Sales, or simply Sales. However, if GOTEAM isn't a member of the System Administrators server role and creates a table called Sales, Sales belongs to GOTEAM and must be qualified as GOTEAM.Sales.

Note: Technically, dbo isn't a "special login," but you may see it referred to as such. You can't log in to a server or database as dbo, but you may be the person who created the database or a set of objects in it.

Permissions

Permissions determine the actions that users can perform on SQL Server or in a database. Permissions are granted according to login ID, group memberships, and role memberships. Users must have appropriate permissions before they can perform any action that changes database definitions or accesses data. Three types of permissions are used in SQL Server:

  • Object permissions

  • Statement permissions

  • Implicit permissions

Object Permissions

Object permissions control access to tables, views, columns, and stored procedures. You control access to these objects by granting, denying, or revoking the ability to execute particular statements or stored procedures. For example, you can grant a user the right to SELECT information from a table, but deny the right to INSERT, UPDATE, or DELETE information in the table. Table 5-1 provides a summary of object permissions.

Table 5-1 Object Permissions

Object Type

Possible Actions

Column

SELECT and UPDATE

Row

N/A (assigned at the table level as they might affect multiple columns)

Stored procedure

EXECUTE

Table

SELECT, INSERT, UPDATE, DELETE, and REFERENCES

View

SELECT, INSERT, UPDATE, and DELETE

Statement Permissions

Statement permissions control administration actions, such as creating a database or adding objects to a database. Only members of the System Administrators role and database owners can assign statement permissions. By default, normal logins aren't granted statement permissions, and you must specifically grant these permissions to logins that aren't administrators. For example, if a user needs to be able to create views in a database, you would assign permission to execute CREATE VIEW. Table 5-2 provides a summary of statement permissions that you can grant, deny, or revoke.

Table 5-2 Statement Permissions

Statement Permission

Description

CREATE DATABASE

Determines whether the login can create databases. The user must be in the master database or a member of the System Administrators server role.

CREATE DEFAULT

Determines whether the user can create a default value for a table column.

CREATE FUNCTION

Determines whether the user can create a user- defined function in the database.

CREATE PROCEDURE

Determines whether the user can create a stored procedure.

CREATE RULE

Determines whether the user can create a table column rule.

CREATE TABLE

Determines whether the user can create a table.

CREATE VIEW

Determines whether the user can create a view.

BACKUP DATABASE

Determines whether the user can back up the database.

BACKUP LOG

Determines whether the user can back up the transaction log.

Implied Permissions

Only members of predefined system roles or database/database object owners can perform implied permissions. Implied permissions for a role can't be changed or applied to other accounts (unless these accounts are made members of the role). For example, members of the System Administrators server role can perform any activity in SQL Server. They can extend databases and kill processes. You can't revoke or assign these rights to other accounts individually.

Database and database object owners also have implied permissions. These permissions allow them to perform all activities with either the database or the object they own, or with both. For example, a user who owns a table can view, add, change, and delete data. That user can also alter the table's definition and control the table's permissions.

Roles

Roles are a lot like Windows groups in that they allow you to easily assign permissions to a group of users and they can have built-in permissions (implicit permissions) that can't be changed. Two types of roles are available:

  • Server roles Applied at the server level

  • Database roles Applied at the database level

Server Roles

You use server roles to grant server administration capabilities. If you make a login a member of a role, users who use this login can perform any tasks permitted by the role. For example, members of the System Administrators role have the highest level of permissions on SQL Server and can perform any type of task.

You set server roles at the server level and you predefine them. This means that these permissions affect the entire server and you can't change the permission set. The list items that follow provide a summary of each server role from the lowest-level role (Database Creators) to the highest-level role (System Administrators).

  • Bulk Insert Administrators (bulkadmin) Designed for domain accounts that need to perform bulk inserts into the database. Members of this role can add members to bulkadmin and execute the BULK INSERT command.

  • Database Creators (dbcreator) Designed for users who need to create, modify, and drop databases. Members of this role can add members to dbcreator and perform these tasks: ALTER DATABASE, CREATE DATABASE, DROP DATABASE, EXTEND DATABASE, RESTORE DATABASE, RESTORE LOG, and sp_renamedb.

  • Disk Administrators (diskadmin) Designed for users who need to manage disk files. Members of this role can add members to diskadmin and perform these tasks: DISK INIT, sp_addumpdevice, sp_diskdefault, and sp_dropdevice.

  • Process Administrators (processadmin) Designed for users who need to control SQL Server processes. Members of this role can add members to processadmin and kill processes.

  • Security Administrators (securityadmin) Designed for users who need to manage logins, create database permissions, and read error logs. Members of this role can add members to securityadmin; grant, deny, and revoke CREATE DATABASE; and read the error logs. In addition, they can also perform these tasks: sp_addlinkedsrvlogin, sp_addlogin, sp_defaultdb, sp_defaultlanguage, sp_denylogin, sp_droplinkedsrvlogin, sp_droplogin, sp_grantlogin, sp_helplogins, sp_remoteoption, and sp_revokelogin.

  • Server Administrators (serveradmin) Designed for users who need to set server-wide configuration options and shut down the server. Members of this role can add members to serveradmin and perform these other tasks: DBCC FREEPROCCACHE, RECONFIGURE, SHUTDOWN, sp_configure, sp_fulltext_service, and sp_tableoption.

  • Setup Administrators (setupadmin) Designed for users who need to manage linked servers and control startup procedures. Members of this role can add members to setupadmin; add, drop, and configure linked servers; and control startup procedures.

  • System Administrators (sysadmin) Designed for users who need complete control over SQL Server and installed databases. Members of this role can perform any activity in SQL Server.

Database Roles

When you want to assign permissions at the database level, you can use database roles. You set database roles on a per database basis, which means that each database has its own set of roles. SQL Server 2000 supports three types of database roles:

  • User-defined standard roles

  • User-defined application roles

  • Predefined (or fixed) database roles

Standard roles allow you to create roles with unique permissions and privileges. You can use standard roles to logically group users together and then assign a single permission to the role rather than having to assign permissions to each user separately. For example, you could create a role called Users that allows users to SELECT, INSERT, and UPDATE specific tables in the database but doesn't allow them to perform any other tasks.

Application roles allow you to create password-protected roles for specific applications. For example, a user could connect through a Web-based application called NetReady; this application would activate the role and the user would then gain the role's permissions and privileges. User or other roles can't be assigned to an application role. Instead, the application role is activated when the application connects to the database.

SQL Server also has predefined database roles. Predefined roles are built in and have permissions that can't be changed. You use predefined roles to assign database administration privileges, and you can assign a single login to multiple roles. These privileges are summarized in the list items that follow.

  • public The default role for all database users. Users inherit the permissions and privileges of the public role and this role represents their minimum permissions and privileges. Any roles that you assign to a user, beyond the public role, add permissions and privileges. If you want all database users to have specific permissions, assign the permissions to the public role.

  • db_accessadmin Designed for users who need to add or remove logins in a database. Members of this role can perform these tasks on the se- lected database: sp_addalias, sp_adduser, sp_dropalias, sp_dropuser, sp_grantdbaccess, and sp_revokedbaccess.

  • db_backupoperator Designed for users who need to back up a database. Members of this role can perform these tasks on the selected database: BACKUP DATABASE, BACKUP LOG, CHECKPOINT, DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKDB, DBCC TEXTALL, DBCC TEXTALLOC, and DBCC UPDATEUSAGE.

  • db_datareader Designed for users who need to view data in a database. Members of this role can select all data from any user table in the database.

  • db_datawriter Designed for users who need to modify any data in any user table in the database. Members of this role can perform these tasks on any objects in the selected database: DELETE, INSERT, and UPDATE.

  • db_ddladmin Designed for users who need to perform tasks related to SQL Server's data definition language (DDL). Members of this role can issue any DDL statement except for GRANT, REVOKE, or DENY. Members can also perform these tasks in the selected database: REFERENCES, sp_changeobjectowner, sp_procoption, sp_recompile, sp_rename, and sp_tableoption.

  • db_denydatareader Designed to restrict access to data in a database by login. Members of this role can deny or revoke Select permissions on any object in the database.

  • db_denydatawriter Designed to restrict modifications permissions in a database by login. Members of this role can deny or revoke INSERT, UPDATE, and DELETE permissions on any object in the database.

  • db_securityadmin Designed for users who need to manage permissions, object ownership, and roles. Members of this role can perform any of the following tasks in the selected database: DENY, GRANT, REVOKE, sp_addapprole, sp_addrole, sp_addrolemember, sp_approlepassword, sp_changeobjectowner, sp_dropapprole, sp_droprole, and sp_droprolemember.

  • db_owner Designed for users who need complete control over all aspects of the database. Members of this role can assign permissions, modify database settings, perform database maintenance, and perform any other administration task on the database.

Managing Server Logins

SQL Server can use Windows domain logins as well as SQL Server's own logins. If you've configured the server for mixed security, you can use both login types. Otherwise, you can use only Windows domain logins.

Viewing and Editing Existing Logins

To view or edit an existing login, follow these steps:

  1. Start Enterprise Manager and then access the server you want to work with.

    In the server's Security folder, select the Logins entry in the left pane. The right pane now displays the current logins, as shown in Figure 5-1. From this information you can learn the following:

    • Name The login name.

    • Type The login type, which is Standard for SQL Server logins, Windows User for domain user accounts, and Windows Group for domain group accounts.

    • Server Access The type of access permission the user has to the server. Permit means the user can access the server. Deny means the user can't access the server.

    • Default Database The default database for the user.

    • Default Language The default language for the user.

    Cc917623.ppc0501(en-us,TechNet.10).gif

    Figure 5-1: Select the Logins entry in the Security folder to display current logins in the right pane.

  2. To view server role and database access permissions for a user, double-click the user's entry in the right pane. This opens the SQL Server Login Properties dialog box.

  3. You can now edit the properties for this account using the fields of the General, Server Roles, and Database Access tabs.

To view a login with Transact-SQL, use sp_helplogins. The syntax and usage for this command is shown as Sample 5-1.

Sample 5-1 sp_helplogins Syntax and Usage

Syntax

sp_helplogins [[@LoginNamePattern =] 'login']
Usage
EXEC sp_helplogins 'goteam'

Creating Logins

You create new logins in Enterprise Manager by using either the Create Login Wizard or the Login Properties dialog box. Because the steps are almost identical with either method, I'll focus on the Login Properties dialog box and let you apply these techniques to the Create Login Wizard dialog boxes if you'd rather use the wizard.

To access the wizard, select the Security folder in Enterprise Manager, and then from the Tools menu, choose Wizards. This displays the Select Wizard dialog box. Click the plus sign next to Database and double-click Create Login Wizard.

Tip If you want to use Windows domain user or group accounts, you must create these accounts in the Windows domain and then create the related SQL Server logins. Ask a domain administrator to set up the necessary accounts.

To create a SQL Server login, follow these steps:

  1. Start Enterprise Manager and then access the server you want to work with.

  2. In the server's Security folder, right-click the Logins entry and select New Login. This opens the SQL Server Login Properties dialog box shown in Figure 5-2.

    Cc917623.ppc0502(en-us,TechNet.10).gif

    Figure 5-2: To create new logins, you can use the SQL Server Login Properties dialog box or the Create Login Wizard.

  3. In the Name field, type the name of the account you want to use, such as Sales or WRSTANEK.

  4. If you're creating a login for a domain account, select the Windows Authentication option button and then use the Domain combo box to select the domain you want to use. You can also type the domain name into the combo box. The domain name should now appear in the Name field.

  5. If you want to create a new SQL Server login, select the SQL Server Authentication option button and then enter the SQL Server password for the login.

  6. Specify the default database and default language for the login. Assigning a default database doesn't give the login permission to access the database. Instead, it merely specifies the database that's used when no database is specified in a command.

  7. Click OK to create the login. If you're creating a SQL Server login, confirm the password by reentering it when prompted.

  8. You haven't assigned any roles or access permissions. Refer to the sections later in this chapter entitled "Configuring Server Roles" and "Controlling Database Access and Administration" to learn how to configure these options.

You can also create logins with Transact-SQL. To create a login for a domain account, use sp_grantlogin, as shown in Sample 5-2.

Sample 5-2 sp_grantlogin Syntax and Usage

Syntax

sp_grantlogin [@loginame =] 'login'

Usage

EXEC sp_grantlogin 'GALAXY\Sales'

To create a new SQL Server login, use sp_addlogin, as shown in Sample 5-3.

Sample 5-3 sp_addlogin Syntax and Usage

Syntax

sp_addlogin [@loginame =] 'login'
   [,[@passwd =] 'password']
   [,[@defdb =] 'database']
   [,[@deflanguage =] 'language']
   [,[@sid =] 'sid']
   [,[@encryptopt =] 'encryption_option']

Usage

EXEC sp_addlogin 'webtr', 'webtester', 'Customer', 'us_english'.

Note: Although sp_grantlogin and sp_addlogin allow users to connect to SQL Server, the logins can't access databases. To configure database access, you need to run sp_grantdbaccess for each database the login needs access to. For details, see the section of this chapter entitled "Controlling Database Access and Administration."

Granting or Denying Server Access

When you create a new login or modify an existing login based on a Windows account, you can explicitly grant or deny access to the server for this login. Explicitly denying access to the server is useful when a particular Windows account should be temporarily restricted from accessing the server.

To grant or deny access for an existing login, complete the following steps:

  1. Start Enterprise Manager and then access the server you want to work with.

  2. In the server's Security folder, select the Logins entry in the left pane.

  3. In the right pane, double-click the account you want to work with. This opens the SQL Server Login Properties dialog box shown previously in Figure 5-2.

  4. To grant access to the server, select the Grant Access option button.

  5. To deny access to the server, select the Deny Access option button.

    Note: Denying access to the server doesn't prevent users from logging in to SQL Server. Instead, it prevents them from using their Windows domain account to log in. Users can still log in if they have a valid SQL Server login ID and password.

  6. Click OK.

You can also grant or deny logins with Transact-SQL. To grant a login for a domain account, use sp_grantlogin, as shown in Sample 5-4.

Sample 5-4 sp_grantlogin Syntax and Usage

Syntax

sp_grantlogin [@loginame =] 'login'

Usage

EXEC sp_grantlogin 'GALAXY\WRSTANEK'

To deny access to the server for the account, use sp_denylogin as shown in Sample 5-5.

Sample 5-5 sp_denylogin Syntax and Usage

Syntax

sp_denylogin [@loginame =] 'login'

Usage

EXEC sp_denylogin ' GALAXY\WRSTANEK '

Removing Logins

When a user leaves the organization or a login is otherwise not needed, you should remove the login from SQL Server. To do this, do the following:

  1. Start Enterprise Manager and then access the server you want to work with.

  2. In the server's Security folder, select the Logins entry in the left pane.

  3. Right-click the login you want to remove and then from the shortcut menu, choose Delete.

  4. When prompted, choose Yes to confirm that you want to delete the login.

The stored procedures that you use to delete SQL Server logins are sp_revokelogin and sp_droplogin. Use sp_revokelogin to delete Windows user and group accounts, as shown in Sample 5-6.

Sample 5-6 sp_revokelogin Syntax and Usage

Syntax

sp_revokelogin [@loginame =] 'login'

Usage

EXEC sp_revokelogin ' GALAXY\WRSTANEK '

Use sp_droplogin to remove a SQL Server login as shown in Sample 5-7.

Sample 5-7 sp_droplogin Syntax and Usage

Syntax

sp_droplogin [@loginame =] 'login'

Usage

EXEC sp_droplogin 'TempUser'

Changing Passwords

You manage Windows user and group accounts in the Windows domain. Users can change their own passwords or ask the Windows administrator to reset their passwords, if necessary. For SQL Server logins, you change passwords through Enterprise Manager using the following steps:

  1. Start Enterprise Manager and then access the server you want to work with.

  2. In the server's Security folder, select the Logins entry in the left pane.

  3. Double-click the login you want to change. This opens the SQL Server Login Properties dialog box.

  4. Type the new password in the Password field and then click OK.

  5. When prompted, reenter the password to confirm it and then click OK.

To change passwords with Transact-SQL, use sp_password, as shown in Sample 5-8.

Sample 5-8 sp_password Syntax and Usage

Syntax

sp_password [[@old =] 'old_password',]
   {[@new =] 'new_password'}
   [,[@loginame =] 'login']

Usage

EXEC sp_password 'changeme' 'h4rt5' 'GOTEAM'

Configuring Server Roles

Server roles set server-wide administrator privileges for SQL Server logins. You can manage server roles by role or by individual logins.

Assigning Roles by Login

To assign or change server roles for a login, follow these steps:

  1. Start Enterprise Manager and then access the server you want to work with.

  2. In the server's Security folder, select the Logins entry in the left pane.

  3. Double-click the login you want to configure. This opens the SQL Server Login Properties dialog box.

  4. Click the Server Roles tab, as shown in Figure 5-3.

  5. Grant server roles by selecting the check boxes next to the roles you want to use. Server roles are discussed in the section entitled "Server Roles."

  6. When you're finished configuring server roles, click OK.

    Cc917623.ppc0503(en-us,TechNet.10).gif

    Figure 5-3: In the Server Roles tab, you grant server roles by selecting the check boxes next to the roles you want to use.

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 Sample 5-9.

Sample 5-9 sp_addsrvrolemember Syntax and Usage

Syntax

sp_addsrvrolemember [@loginame =] 'login', [@rolename =] 'role'

Usage

EXEC sp_addsrvrolemember 'GALAXY\WRSTANEK' 'sysadmin'

The sp_dropsrvrolemember stored procedure removes a login from a role and you can use it as shown in Sample 5-10.

Sample 5-10 sp_dropsrvrolemember Syntax and Usage

Syntax

sp_dropsrvrolemember [@loginame =] 'login', [@rolename =] 'role'

Usage

EXEC sp_dropsrvrolemember 'GALAXY\WRSTANEK' 'sysadmin'

Assigning Roles to Multiple Logins

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:

  1. Start Enterprise Manager and then access the server you want to work with.

  2. In the server's Security folder, select the Server Role entry in the left pane.

  3. In the right pane, double-click the server role you want to configure. This opens the Server Role Properties dialog box shown in Figure 5-4.

  4. To add logins, click Add, and then in the Add Members dialog box, select the logins to add. Multiple logins can be selected by holding down Ctrl or Shift as you click login names.

    Cc917623.ppc0504(en-us,TechNet.10).gif

    Figure 5-4: The Server Role Properties dialog box lets you configure multiple logins to use a particular server role.

  5. To remove a login, select a login and then click Remove.

  6. Click the Permissions tab to see the permissions associated with this server role.

  7. When you're finished configuring server roles, click OK.

Controlling Database Access and Administration

You control database access and administration with database users and roles. Database users are the logins that have the right to access the database. Database access roles set administration privileges and other database permissions.

Assigning Access and Roles by Login

For individual logins, you can grant access to databases and assign roles as follows:

  1. Start Enterprise Manager and then access the server you want to work with.

  2. In the server's Security folder, select the Logins entry in the left pane.

  3. Double-click the login you want to configure. This opens the SQL Server Login Properties dialog box.

  4. Click the Database Access tab, as shown in Figure 5-5.

    Cc917623.ppc0505(en-us,TechNet.10).gif

    Figure 5-5: Use the Database Access tab to grant database access, and then select roles the user should have on the currently selected database.

  5. Select the check box for a database that the login should have access to. Then, in the Permit In Database Role list box, select the check boxes next to the database roles that this login should have on the currently selected database.

  6. Repeat step 5 for other databases the login should have access to.

  7. When you're finished configuring database roles, click OK.

Assigning Roles for Multiple Logins

At the database level, you can assign database roles to multiple logins. To do this, complete the following steps:

  1. Start Enterprise Manager and then, using the entries in the left pane, work your way down to the Databases folder.

  2. In the Databases folder, click the plus sign (+) next to the database you want to work with.

  3. In the left pane, select Roles to display a list of database roles in the right pane.

  4. Double-click the role you want to configure. This opens the Database Role Properties dialog box shown in Figure 5-6.

  5. To add role members, click Add. Then, in the Add Role Members dialog box, select the logins you want to add. You can select multiple logins by holding down Ctrl or Shift as you click login names.

  6. To remove a role member, select a login and then choose Remove.

  7. When you're finished configuring database roles, click OK.

    Cc917623.ppc0506(en-us,TechNet.10).gif

    Figure 5-6: The Database Role Properties dialog box lets you configure multiple logins to use a particular database role.

Creating Standard Database Roles

Although predefined roles have a specific set of permissions that you can't change, you can set permissions for roles you create for a particular database. For example, suppose that a database has three different types of users: normal users who need to view data, managers who need to be able to modify data, and developers who need to be able to modify database objects. In that case you can create three roles to handle these user types. Then you need to manage only these roles and not the many different user accounts.

To create a standard database role, complete the following steps:

  1. Start Enterprise Manager and then work your way down to the Databases folder using the entries in the left pane.

  2. In the Databases folder, click the plus sign (+) next to the database you want to work with.

  3. Right-click Roles and then from the shortcut menu, choose New Database Role. This opens the Database Role Properties dialog box shown in Figure 5-7.

  4. Type a name for the role in the Name field.

    Tip Use a name that's short but descriptive, such as Normal Users, Editors, or Testers and Developers.

    Cc917623.ppc0507(en-us,TechNet.10).gif

    Figure 5-7: When you create a database role, you can edit the Name field but you can't configure permissions until after you create the role.

  5. Select the Standard Role option button.

  6. To add role members, click Add. Then, in the Add Role Members dialog box, select the logins to add. You can select multiple logins by holding down Ctrl or Shift as you click login names.

  7. Click OK.

  8. Enterprise Manager's right pane should display the new role. Double-click its entry to reopen the Database Role Properties dialog box.

  9. Click Permissions and then use the Permissions tab to configure database access permissions for this role. For more information, see the section of this chapter entitled "Managing Database Permissions."

Creating Application Database Roles

Application roles are designed to be used by applications that access the database and don't have logins associated with them. You can configure an application role by completing the following steps:

  1. Start Enterprise Manager and then, using the entries in the left pane, work your way down to the Databases folder.

  2. In the Databases folder, click the plus sign (+) next to the database you want to work with.

  3. Right-click Roles and then from the shortcut menu, choose New Database Role. This opens the Database Role Properties dialog box shown previously in Figure 5-7.

  4. Type a name for the role in the Name field.

  5. Select the Application Role option button and then type a password for this role in the Password field. You won't be asked to confirm this password.

  6. Click OK.

  7. Enterprise Manager's right pane should display the new role. Double-click its entry to reopen the Database Role Properties dialog box.

  8. Click Permissions and then use the Permissions tab to configure database access permissions for this role. For more information, see the section of this chapter entitled "Managing Database Permissions."

Revoking Access Rights and Roles by Login

To revoke access rights or to remove a user from a role in a database, complete the following steps:

  1. Start Enterprise Manager and then work your way down to the Security folder using the entries in the left pane.

  2. In the Security folder, select the Logins entry in the left pane.

  3. Double-click the login you want to configure. This opens the SQL Server Login Properties dialog box.

  4. Click the Database Access tab.

  5. Clear the check box for a database that the login should not have access to. Then, in the Permit In Database Role list box, clear the check boxes next to the database roles that this login should not have on the currently selected database.

  6. Repeat step 5 for other databases the login should not have access to or rights on.

  7. When you're finished, click OK.

Deleting User-Defined Roles

To delete a user-defined role, complete the following steps:

  1. Start Enterprise Manager and then work your way down to the Databases folder using the entries in the left pane.

  2. In the Databases folder, click the plus sign (+) next to the database you want to work with.

  3. In the left pane, click Roles.

  4. Select the role you want to delete and then press the Delete key.

  5. When prompted, choose Yes to confirm that you want to delete the role.

Note: User-defined roles cannot be deleted if they have members. Edit the properties for the role, deleting any currently listed members, and then delete the role.

Transact-SQL Commands for Managing Access and Roles

SQL Server provides different commands for managing database access and roles. These commands are summarized in Sample 5-11.

Sample 5-11 Commands for Managing Database Access and Roles

Database Access

sp_grantdbaccess [@loginame =] 'login'
   [,[@name_in_db =] 'name_in_db' [OUTPUT]]
sp_revokedbaccess [@name_in_db =] 'name'

Predefined Roles

sp_dbfixedrolepermission [[@rolename =] 'role']

Database Standard Roles

sp_addrole [@rolename =] 'role' [,[@ownername =] 'owner']
sp_droprole [@rolename =] 'role
sp_helprole [[@rolename =] 'role']

Database Role Members

sp_addrolemember [@rolename =] 'role',
   [@membername =] 'security_account'
sp_droprolemember [@rolename =] 'role',
   [@membername =] 'security_account'
sp_helprolemember [[@rolename =] 'role']

Application Roles

sp_addapprole [@rolename =] 'role', [@password =] 'password'
sp_dropapprole [@rolename =] 'role'
sp_setapprole [@rolename =] 'role' ,
   [@password =] {Encrypt N 'password'} | 'password'
   [,[@encrypt =] 'encrypt_style']

Managing Database Permissions

You can assign database permissions by the database owner, members of sysadmin, and members of securityadmin. The available permissions include

  • Grant Gives permission to perform the related task. With roles, all members of the role inherit the permission.

  • Revoke Removes prior grant permission but doesn't explicitly prevent a user or role from performing a task. A user or role could still inherit grant permission from another role.

  • Deny Explicitly denies permission to perform a task and prevents the user or role from inheriting the permission. Deny takes precedence over all other grant permissions.

Note: Deny is a Transact-SQL command and isn't part of the ANSI SQL-92 standard.

You can assign grant, deny, and revoke permissions at the database level or the object level. You can also assign permissions using database roles. For more information, see the section of this chapter entitled "Controlling Database Access and Administration."

Assigning Database Permissions for Statements

At the database level, you can grant, revoke, or deny permission to execute data definition language statements, such as CREATE TABLE or BACKUP DATABASE. These statements were previously summarized in Table 5-2.

In Enterprise Manager, you grant, revoke, or deny database permissions for statements by completing the following steps:

  1. Work your way down to the Databases folder using the entries in the left pane.

  2. Right-click the database you want to work with and then from the shortcut menu, choose Properties.

  3. Click the Permissions tab, as shown in Figure 5-8.

  4. To assign default permissions for all users, assign permissions to the public role. To assign permissions for individual users or roles, click the entries to the right of the User/Role name. A check mark grants permission. A red X denies permission. Clear a check mark to revoke permission.

  5. Click OK to assign the permissions.

With Transact-SQL, you use the GRANT, REVOKE, and DENY commands to assign permissions. Sample 5-12 shows the syntax and usage of GRANT, Sample 5-13 shows the syntax and usage of REVOKE, and Sample 5-14 shows the syntax and usage of DENY.

Cc917623.ppc0508(en-us,TechNet.10).gif

Figure 5-8: The Permissions tab lets you assign statement permissions at the database level. The key icon indicates a role. The person icon indicates a user.

Sample 5-12 GRANT Syntax and Usage

Syntax

GRANT {ALL | statement[,...n]}
TO security_account[,...n]
GRANT
   {ALL [PRIVILEGES] | permission[,...n]}
   {
      [(column[,...n])] ON {table | view}
      | ON {table | view}[(column[,...n])]
      | ON {stored_procedure | extended_procedure}
   }
TO security_account[,...n]
[WITH GRANT OPTION]
[AS {group | role}]

Usage

GRANT CREATE DATABASE, CREATE TABLE
TO Users, [GALAXY\Sales]
GRANT SELECT
ON customer..customers
TO public
GRANT INSERT, UPDATE, DELETE
ON customer..customers
TO Devs, Testers

Sample 5-13 REVOKE Syntax and Usage

Syntax

REVOKE {ALL | statement[,...n]}
FROM security_account[,...n]
REVOKE [GRANT OPTION FOR]
   {ALL [PRIVILEGES] | permission[,...n]}
   {
      [(column[,...n])] ON {table | view}
      | ON {table | view}[(column[,...n])]
      | {stored_procedure | extended_procedure}
   }
{TO | FROM}
   security_account[,...n]
[CASCADE]
[AS {group | role}]

Usage

REVOKE CREATE TABLE, CREATE DEFAULT
FROM Devs, Testers
REVOKE INSERT, UPDATE, DELETE
FROM Users, [GALAXY\Sales]

Sample 5-14 DENY Syntax and Usage

Syntax

DENY{ALL | statement[,...n]}
TO security_account[,...n]
DENY
   {ALL [PRIVILEGES] | permission[,...n]}
   {
      [(column[,...n])] ON {table | view}
      | ON {table | view}[(column[,...n])]
      | ON {stored_procedure | extended_procedure}
   }
TO security_account[,...n]
[CASCADE]

Usage

DENY CREATE TABLE
   TO Devs, Testers
DENY INSERT, UPDATE, DELETE
   ON customer..customers
   TO Users, [GALAXY\Sales]

Object Permissions by Login

Object permissions apply to tables, views, and stored procedures. Permissions you assign to these objects include SELECT, INSERT, UPDATE, and DELETE. A summary of permitted actions by object was listed previously in Table 5-1.

In Enterprise Manager, you grant, revoke, or deny object permissions by completing the following steps:

  1. Work your way down to the Databases folder using the entries in the left pane.

  2. Click the plus sign (+) next to the database you want to work with and then select Users.

  3. In the right pane, you should see a list of database users. Double-click the user you want to configure. This opens the Database User Properties dialog box.

  4. Click Permissions to display the Permissions tab, as shown in Figure 5-9.

    Cc917623.ppc0509(en-us,TechNet.10).gif

    Figure 5-9: Use the Permissions tab to assign object permissions. An eyeglasses icon indicates a view. A table icon indicates a table. An icon with the wavy blue lines indicates a stored procedure.

  5. To assign object permissions, select the List All Objects option button. You use the entries to the right of the Object and Owner names to assign permissions. A check mark grants permission. A red X denies permission. Clear a check mark to revoke permission.

  6. Click Apply or OK to assign the permissions.

Object Permissions for Multiple Logins

You can also assign permission by object and in this way assign object permissions for multiple logins. To do this, complete the following steps:

  1. Start Enterprise Manager and using the entries in the left pane, work your way down to the Databases folder.

  2. Click the plus sign (+) next to the database you want to work with and then select the type of objects you want to work with, either Tables, Views, or Stored Procedures.

  3. In the right pane, double-click the table, view, or stored procedure you want to configure. This opens a Properties dialog box.

  4. In the Properties dialog box, click Permissions. This opens the Object Properties dialog box shown in Figure 5-10.

    Cc917623.ppc0510(en-us,TechNet.10).gif

    Figure 5-10: Use the Object Properties dialog box to assign permissions by object rather than by login or role.

  5. To assign permissions for logins, select the List All Users option button. You use the entries to the right of the User/Database Roles/Public names column to assign permissions. A check mark grants permission. A red X denies permission. Clear a check mark to revoke permission.

Transact-SQL commands for assigning permissions were listed previously in the section of this chapter entitled "Assigning Database Permissions for Statements."

Link
Click to order