Lesson 3: Granting Access to Databases | Microsoft SQL Server 7.0 System Administration Training Kit

[Previous] [Next]

After a user is connected to SQL Server, she needs to be given access to one or more databases. A user gains access to databases based on user accounts or roles created and managed separately in each database. Although you can create a login for an individual user, normally you will create logins for Windows NT groups, allowing all members of the group access to the database.

If a user is connected to SQL Server using a Windows NT_authenticated login, that user is represented by her own Windows NT user account as well as the accounts of all Windows NT groups of which she is a member. The user can therefore gain access to a database if database access has been granted to any of these accounts.

If a user is connected to SQL Server using a SQL Server_authenticated login, the user is represented by the SQL Server login only. The user can therefore gain access to a database only if database access has been granted to this login.

After this lesson, you will be able to

Grant database access to Windows NT user and group accounts and SQL Server logins
Describe the two default database user accounts
Assign logins to fixed server roles
Assign security accounts to fixed database roles
Create and assign security accounts to user-defined database roles

Estimated lesson time: 60 minutes

Granting Database Access to Logins

To access a database, a login (which can be any of the following: a Windows NT user or group account that has been granted access to SQL Server [see Figure 11.5], a SQL Server login [see Figure 11.6], or one of the default SQL Server logins) uses either an assigned database user account or one of the default database user accounts. User accounts can be assigned to Windows NT users, Windows NT groups, or SQL Server logins.

click to view at full size.

Figure 11.5 Login authentication and database access using Windows NT_authenticated logins

click to view at full size.

Figure 11.6 Login authentication and database access using SQL Server_authenticated logins

To assign a user account to a login, you can use SQL Server Enterprise Manager or execute the sp_grantdbaccess system stored procedure. Only database owners and database access administrators can assign a user account to a login. An entry is added to the sysusers table in the database to which access is granted.

Exercise: Granting Database Access with SQL Server Enterprise Manager

In this exercise, you will grant some of the logins you created earlier in this chapter access to the StudyNwind database. Two methods are provided to give you practice using different parts of SQL Server Enterprise Manager.

To grant database access with SQL Server Enterprise Manager

Perform the following steps for the logins Carl and Cathy, which were created in the Exercise, "Exercise: Adding a SQL Server Login Using SQL Server Enterprise Manager" in Lesson 1 of this chapter.

In SQL Server Enterprise Manager, expand your server.

Expand Security, and then click Logins.

In the details pane, right-click the login to modify, and then click Properties.

On the Database Access tab, check the box next to StudyNwind.

Click OK to close the SQL Server Login Properties dialog box and assign database access to the login.

Perform the following steps for the logins STUDYSQL\Paul and STUDYSQL \Customer_mgmt created in an earlier exercise.

In SQL Server Enterprise Manager, expand your server.

Expand Databases, then expand the StudyNwind database.

Right-click Users, then click New Database User.

Under Login Name, click the login name.

Click OK to close the Database User Properties — New User dialog box and assign database access to the login.

Granting Database Access with sp_grantdbaccess

The syntax for the sp_grantdbaccess statement is as follows:

 sp_grantdbaccess 'login' [,'name_in_db']

The following example uses the sp_grantdbaccess statement to give the logins Carl and Paul access to the database.

 sp_grantdbaccess 'Carl' sp_grantdbaccess 'STUDYSQL\Paul'

IMPORTANT
For scripts that provide examples of assigning the logins from the previous exercise, see C:\Sqladmin\Exercise\Ch11\Sqllogin.sql and C:\Sqladmin \Exercise\Ch11\Ntlogin.sql installed on your hard disk drive from the Supplemental Course Materials CD-ROM. These scripts can be reviewed and executed in the Query Analyzer.

The login parameter is the name of the login for the new account in the database. It can be any Windows NT user, Windows NT group, or SQL Server login.

The name_in_db parameter is an optional name for the account in the database.

TIP
It is possible to use the sp_grantdbaccess system stored procedure to grant access to a Windows NT user or group that has not been added as a login. This is not possible in SQL Server Enterprise Manager. Granting access in this way allows a user to connect to the SQL Server using one Windows NT account and then be granted access to a database based on another account. Remember that every user is represented by her own Windows NT user account as well as the accounts of all Windows NT groups of which she is a member.

The following table lists other system stored procedures that you can use for managing database access.

System stored procedure	Description
sp_revokedbaccess	Removes a security account from the current database
sp_change_users_login	Changes the relationship between a SQL Server login and a SQL Server user in the current database

Default User Accounts

Each database within SQL Server also has two default user accounts: dbo and guest.

The Database Owner (dbo) Account

The sa login account and members of the System Administrators (sysadmin) role are mapped to a special user account inside all databases called dbo. Any object that a system administrator creates automatically belongs to dbo. The dbo user cannot be dropped.

The guest User Account

The guest user account allows logins without user accounts access to a database. Logins assume the identity of the guest user account when both of the following conditions are met:

The login has access to SQL Server but does not have access to the database through its own user account.

The database contains a guest user account.

Permissions can be applied to the guest user account as if it were any other user account. You can drop and add the guest user to any database except the master and tempdb databases. By default, the guest user account is not given any permissions, but it is a member of the public role. You should therefore be careful when assigning permissions to the public role; drop the guest user account if necessary.

Assigning Logins to Roles

Roles provide a means of assembling users into a single unit to which permissions can be applied.

NOTE
Roles replace the SQL Server 6.5 concepts of aliases and groups.

SQL Server provides predefined fixed server and database roles for common administrative functions so that you can easily grant a selection of administrative permissions to a particular user.

You can also create your own database roles to represent work that a class of employees in your organization performs. As employees rotate into certain positions, you simply add them as members of the role; as they rotate out of the positions, remove them from the role. You do not have to grant and revoke permissions repeatedly as employees commence or leave various positions. If the function of a position changes, it is easy to change the permissions for the role and have the changes applied automatically to all members of the role.

Fixed Server Roles

The fixed server roles provided by SQL Server are listed in the following table.

Fixed server role	Description
Sysadmin	Can perform any activity in SQL Server
Serveradmin	Can configure serverwide settings
Setupadmin	Can install replication and manage extended procedures
Securityadmin	Can manage server logins
Processadmin	Can manage processes running in SQL Server
Dbcreator	Can create and alter databases
Diskadmin	Can manage disk files

The permissions of the sysadmin fixed server role span all of the other fixed server roles. The sysadmin role is the equivalent of the sa login.

Fixed server roles provide groupings of administrative privileges at the server level. They are managed independently of user databases and are stored in the master..syslogins system table. It is not possible to add new server roles.

Assigning a Login Account to a Fixed Server Role

You can use SQL Server Enterprise Manager or the sp_addsrvrolemember system stored procedure to add a login account as a member of a fixed server role. Only members of the fixed server roles can add a login account as a member of a fixed server role.

To use SQL Server Enterprise Manager to assign a login to a fixed server role

Expand your server group, then expand your server.

Expand Security, and click Server Roles.

In the details pane, right-click the role Security Administrators, and then click Properties.

On the General tab, click Add.

Click the login to add: STUDYSQL\Paul.

Click OK twice to close the dialog boxes and assign STUDYSQL\Paul to the Security Administrators fixed server role.

Using sp_addsrvrolemember to Assign a Login to a Fixed Server Role

The syntax for the sp_addsrvrolemeber statement is as follows:

 sp_addsrvrolemember 'login', 'role'

The following example adds the login Paul to the securityadmin role.

 sp_addsrvrolemember 'STUDYSQL\Paul', 'securityadmin'

NOTE
For a script that adds a login to a fixed server role, see C:\Sqladmin\Exercise \Ch11\Ntlogin.sql installed on your hard disk drive from the Supplemental Course Materials CD-ROM. You can review and execute this script in the Query Analyzer.

When you add a login to a server role, the corresponding row for the login in the syslogins table is updated to indicate that the login is a member of the role. The login then has the permissions that are associated with the server role.

Consider the following facts about assigning login accounts to fixed server roles:

Fixed server roles cannot be added, modified, or removed.

Any member of a fixed server role can add other login accounts to that role.

You can add a Windows NT user or group to a role, even if the user or group has not yet been added as a login. The user or group will be added as a login automatically when you execute sp_addsrvrolemember.

The sp_addsrvrolemember system stored procedure cannot be executed within a user-defined transaction.

Use the sp_dropsrvrolemember system stored procedure to remove a member from a fixed server role.

Fixed Database Roles

The fixed database roles provided by SQL Server are listed in the following table.

Fixed database role	Description
db_owner	Can perform the activities of all database roles, as well as other maintenance and configuration activities in the database
db_accessadmin	Can add or remove Windows NT groups, Windows NT users, and SQL Server users in the database
db_datareader	Can see any data from all user tables in the database
db_datawriter	Can add, change, or delete data from all user tables in the database
db_ddladmin	Can add, modify, or drop objects in the database
db_securityadmin	Can manage roles and members of SQL Server database roles, and can manage statement and object permissions in the database
db_backupoperator	Can back up the database
db_denydatareader	Cannot see any data in the database, but can make schema changes
db_denydatawriter	Cannot change any data in the database

The permissions of the db_owner fixed database role span all of the other fixed database roles.

Fixed database roles provide groupings of administrative privileges at the database level. Fixed database roles are stored in the sysusers system table of each database.

The public Role

The public role is a special database role to which every database user belongs. The public role

Maintains all default permissions for users in a database

Cannot have users, groups, or roles assigned to it because users, groups, and roles already belong by default

Is contained in every database, including master, msdb, tempdb, model, and all user databases

Cannot be dropped

Without being granted any specific permissions, a user possesses the permissions that are granted to the public role and can

Execute statements that do not require permissions, such as the PRINT statement

View system table information and execute certain system stored procedures to retrieve information from the master database and user databases to which she has access

Gain access to any database with a guest account

NOTE
In the pubs and Northwind databases, the public role has been granted all permissions. Security is set this way only because these are sample databases; you should never grant all permissions to the public role in production databases.

Assigning a Security Account to a Fixed Database Role

Use SQL Server Enterprise Manager or the sp_addrolemember system stored procedure to add a security account as a member of a fixed database role. Only members of the db_owner role can execute the sp_addrolemember system stored procedure.

To use SQL Server Enterprise Manager to assign security accounts to a fixed database role

Expand your server group, and then expand your server.

Expand Databases, and then expand the StudyNwind database.

Click Users.

In the details pane, right-click Cathy, and then click Properties.

Under Database Role Membership, click db_datareader and db_datawriter. (Make sure the boxes next to the roles are checked.)

Click OK to close the dialog box and add Cathy to the db_datareader and db_datawriter fixed database roles.

In the console tree, click Roles.

In the details pane, right-click the role db_datareader, and then click Properties.

Under User, click Add.

Select Carl to add.

Click OK twice to close the dialog boxes and add Carl to the db_datareader fixed database role.

Using sp_addrolemember to Assign Security Accounts to a Fixed Database Role

The syntax for the sp_addrolemember statement is as follows:

 sp_addrolemember 'role', 'security_account'

The following example adds the user Carl to the db_datareader role.

 sp_addrolemember 'Carl', 'db_datareader'

NOTE
For a script containing examples of adding users to fixed database roles, see C:\Sqladmin\Exercise\Ch11\Sqllogin.sql installed on your hard disk drive from the Supplemental Course Materials CD-ROM. You can review and execute this script using the Query Analyzer.

Consider the following facts when you assign security accounts to a fixed database role:

Fixed database roles cannot be added, modified, or removed.

Any member of a fixed database role can add other login accounts to that role.

Use the sp_droprolemember system stored procedure to drop a security account from a role.

User-Defined Database Roles

Creating a user-defined database role allows you to create a group of users with a set of common permissions. Add a user-defined role to the database

When a group of people needs to perform a specified set of activities in SQL Server and no applicable Windows NT group exists

If you do not have permissions to manage Windows NT user accounts

When you are using Mixed Mode authentication

For example, a company may form a new Charity Event committee that includes employees from different departments at several different levels. These employees need access to a special project table in the database. A Windows NT group does not exist that includes only these employees, and there is no other reason to create one in Windows NT. You could create a user-defined role, CharityEvent, for this project and then add individual Windows NT user accounts to the role. When permissions are applied, the individual user accounts in the role gain access to the project table.

Creating a User-Defined Database Role

Use SQL Server Enterprise Manager or the sp_addrole system stored procedure to create a new database role. An entry is added to the sysusers table of the current database for each user-defined role. Only members of the db_securityadmin or db_owner roles can execute sp_addrole.

Using sp_addrole to Create a User-Defined Database Role

The syntax for the sp_addrole stored procedure is as follows:

 sp_addrole 'role', 'owner'

The following example uses the sp_addrole stored procedure to create the Cust_mgmt role.

 sp_addrole 'Cust_mgmt'

The owner parameter must be a user or role in the current database and defaults to dbo.

Consider the following facts and guidelines when you create a database role:

The sp_addrole system stored procedure adds a new SQL Server role to the current database.

When you apply permissions to the role, each member of the role gains the effects of the permission as if the permission were applied directly to the member's own account.

Assigning a Security Account to a User-Defined Database Role

After you add a role, use SQL Server Enterprise Manager or the sp_addrolemember system stored procedure to add users or roles as members of the role. Only members of the db_owner fixed database role or a role owner can execute sp_addrolemember to add a member to a user-defined database role.

To use SQL Server Enterprise Manager to create a user-defined database role and assign a security account to the role

Expand your server group, and then expand your server.

Expand Databases, and then expand the StudyNwind database.

Right-click Roles, and then click New Database Role.

Enter the name of the new role: Cust_mgmt.

Click Add to add members to the standard role.

Select Carl and Cathy.

Click OK twice to close the dialog boxes and add the new user-defined role and its members.

Using sp_addrolemember to Assign a Security Account
to a User-Defined Database Role

The syntax for the sp_addrolemember stored procedure is as follows.

 sp_addrolemember 'role', 'security_account'

The following example uses the sp_addrolemember to add Carl to the Cust_mgmt role.

 sp_addrolemember 'Cust_mgmt', 'Carl'

NOTE
For a script that has an example of adding a user-defined database role and assigning users to the role, see C:\Sqladmin\Exercise\Ch11\Sqllogin.sql installed on your hard disk drive from the Supplemental Course Materials CD-ROM. You can review and execute this script using the Query Analyzer.

Consider the following facts when you assign security accounts to a user-defined database role:

When you use the sp_addrolemember system stored procedure to add a security account to a role, any permissions applied to the role are applied to the new member.

You can add a SQL Server role as a member of another SQL Server role, but you cannot create recursive roles. Therefore, role A cannot be added as a member of role B if role B is already a member of role A. Furthermore, role A cannot be added as a member of role C if role B is already a member of role A and role C is already a member of role B.

Nesting roles multiple times can decrease system performance.

The following table lists additional system stored procedures that you can use for managing database roles.

System stored procedure	Description
sp_droprole	Drops a SQL Server role from the current database
sp_droprolemember	Drops a security account from a SQL Server role

Exercise: Testing the Accounts You Have Created

In this exercise, you will test the various accounts that you have created to get a better understanding of each of the different types of login, user, and role.

To test the logins, users, and roles that you have created and assigned

Start SQL Server Query Analyzer and, by using SQL Server authentication, connect as Carl with the password password.

To what database are you connected, and why?

Answer

Execute a query to retrieve data from the Products table. For example,

 SELECT productname FROM Products

Did you receive any results? Why or why not?

Answer

Execute a query to change data in the Products table. For example:

 UPDATE Products
   SET productname = 'Tofu, unsalted'
   WHERE productname = 'Tofu'

Was the update successful? Why or why not?

Answer

Select Connect from the File menu and open a new connection logged on as Cathy. What happens if you perform steps 2 and 3 logged on as Cathy?

Answer

Log off from Windows NT and log on again as STUDYSQL\Carl with the password password.

Start SQL Server Query Analyzer and connect with Windows NT authentication.

Notice that you cannot provide a login name or password when you connect to SQL Server with Windows NT authentication and that your user name is displayed in the title bar of the query window.

How did Carl connect to the database when his Windows NT login was not authorized to use SQL Server?

Answer

Log off from Windows NT and log on again as STUDYSQL\Max with the password password.

Start SQL Server Query Analyzer and connect with Windows NT authentication.

What happens and why?

Answer

Lesson Summary

In this lesson you learned that a user needs to be given access to databases based on user accounts or roles created and managed separately in each relevant database. Although you can create a user account for an individual user, normally you will create user accounts for Windows NT groups, giving all the members of the group access to the database.

A user connected to SQL Server using a Windows NT_authenticated login is represented by her own Windows NT user account and the accounts of all Windows NT groups of which she is a member. This means that the user can gain access to a database if database access has been granted to any of these accounts.

A user connected to SQL Server using a SQL Server_authenticated login is represented by the SQL Server login only. This means that the user can gain access to a database only if database access has been granted to this login.