In our previous article we examined authentication with MSDE. In this article we will take a close look at authorization. We will see how to use and manage database roles, and how to grant, revoke, and deny permissions to database objects and statements. If you need to install the Web Data Administrator, please refer to the previous article for links and additional background information.
Authorization in MSDE revolves around database users and database roles. To gain access to a database, a login (covered in the previous article) must map to a database specific user account.
To see a list of users in a database with Microsoft’s Web Data Administrator (WDA), login and select a database from the list of available databases on the server. For our examples we will be choosing the Northwind database. The next screen should present a list of tables in the database, and also links to database tools on the left hand side of the screen. Click on the Users link to come to the following screen.
We can see there are two users. The first, the dbo user, logs into the database with the sa login. To understand this relationship between users, logins, and the special names like dbo and guest, we will need to discuss the special users in every SQL Server database.
Special Users
There are two special users in MSDE and SQL Server. The first user is present in every database, this is the dbo user. The dbo user (short for database owner) has permissions to perform any activity in a database. You cannot delete the dbo user. MSDE maps administrators to the dbo user implicitly.
The second special user is the guest user. The guest user is not present in a database by default, and you can remove the guest user if present. If a person has a valid login to the database server, but does not have a user account for a database, the person can use the guest account if one is present.
Managing User Accounts
Using the guest account for access to the database is not secure. Instead try to add a user account into each database for each server login requiring access. A login can map to different user accounts in different databases, the names do not need to be the same. In the following section we will discuss how to add, delete, and view user accounts with Web Data Administrator and with system stored procedures.
Adding A User Account
You can add a new user to a database with the Create new User link on the last screen we visited. In the following screen shot you can see the application allows us to select from a list of server logins that have not been given user accounts in the database, and a textbox to type in the user name to give the login (one is provided by default based on the login name). Once you click Create User, the next screen will want you to select roles for the login, for now just select the defaults.
Underneath the covers, the Web Data Administrator is mapping a login to a user account in the current database using the sp_grantdbaccess stored procedure. The first parameter to the stored procedure is the login name, the second parameter is the name of the user account to create and associate with the login. Assuming we are in the Northwind database as an administrator, we can map the accounting login to a user account named accounting with the following command:
exec sp_grantdbaccess 'ComputerName\accounting', 'accounting'
The user account name does not need to reflect the login name. For example, we could have also named the user account ‘acct’. Let's say one of the members of the accounting group is Donna. We can associate Donna with her own user account:
exec sp_grantdbaccess 'ComputerName\Donna', 'Donna'
When Donna logs in, she will map to the user account specified for her login (Donna), even though she is a member of the accouting group with access. MSDE will always try to match a login to an individual account before looking for group membership.
Removing a User Account
You can delete a user account from the database using the Delete link in the user’s row in WDA. You can also remove an account with the system stored procedure sp_revokedbaccess.
exec sp_revokedbaccess 'Donna'
Donna can still login to the database if she is a member of the accounting group under Windows. You cannot remove the dbo user, and you cannot remove a user who owns an object in the current database.
Managing Roles
Roles are similar to groups in Windows. A user may be a member in more than one role.
Before you create custom roles, take a look at the built in roles to see if they might fit your needs. There are several fixed roles provided by the database engine at both the server level and the database level. We begin to cover these topics in the next section.
Server Roles
Adding a user to a fixed server role will grant the user all of the permissions associated with the server role. Server roles are “fixed” because you cannot add or remove these roles, nor can you modify the permissions assigned to these roles. The following table gives the name of each server roles and a general description of the permissions associated with the role.
| Fixed Server Role | Description |
| sysadmin | This is the administrative role. Users in this role can perform any activity on the server. The BUILTIN\Administrator group is a member of this role by default, as is anyone logging in as sa. |
| serveradmin | A user in this role can modify configuration settings on the server, and shut the server down. |
| setupadmin | Can add and remove remote (linked) servers. |
| securityadmin | This role manages logins and can change passwords. |
| processadmin | This role manages processes in the engine, and can kill connections. |
| dbcreator | A dbcreator can add, modify, and remove databases. |
| diskadmin | Manages the physical disk files used by the server for data storage. |
| Bulkadmin | A bulkadmin can perform bulk inserts to the database. |
To view the specific permissions assigned to each fixed server role, execute the sp_srvrolepermission stored procedure. WDA does not expose the sp_srvrolepermission stored procedure in the GUI, but here is example outout for the command.
exec sp_srvrolepermission 'dbcreator' ServerRole Permission dbcreator Add member to dbcreator dbcreator ALTER DATABASE dbcreator CREATE DATABASE dbcreator DROP DATABASE dbcreator Extend database dbcreator RESTORE DATABASE dbcreator RESTORE LOG dbcreator sp_renamedb
You can add database users to a fixed server role in WDA. Click on Server Tools -> Security on the left hand side of the browser, then click on the Server Roles icon. Finally, click Add or Edit Logins on the right hand side of the row with the role you want to edit. The following is a screen shot editing the sysadmin role.
You can also add database users to a fixed server role with the sp_addsrvrolemember stored procedure. Since these are server level roles, not database roles, you must add a login name, not a user name from a database. For example, if Donna still has a valid login we could make Donna a member of the sysadmin group with the following command.
exec sp_addsrvrolemember 'ComputerName\Donna', 'sysadmin'
You can view members in each server role by executing sp_helpsrvrolemember. To remove a user from a server role, execute the sp_dropsrvrolemember procedure.
exec sp_dropsrvrolemember 'ComputerName\Donna', 'sysadmin'
Fixed Database Roles
Adding a user to a fixed database role will grant the user all of the permissions associated with the database role for the given database. These roles are “fixed” because you cannot add or remove these roles from a database, nor can you modify the permissions assigned to these roles.
| Fixed Database Role | Description |
| db_owner | Can perform any activity in the database. |
| db_accessadmin | Can add or remove users in the database. |
| db_securityadmin | Manages permissions, roles, and role memberships. |
| db_ddladmin | Can issue data definition language commands to create, drop, and alter objects. |
| db_backupoperator | Is able to perform backup and diagnostic operations. |
| db_datareader | Can issues SELECT queries on any user table in the database. |
| db_datawriter | Is able to modify the data in any user table. |
| db_bulkadmin | A bulkadmin can perform bulk inserts to the database. |
| db_denydatawriter | Denies modification permissions to any member of the role |
To uncover the specific permissions assigned to a fixed database role, use the sp_dbfixedrolepermission stored procedure. (there is no WDA equivalent). Sample output for the db_datawriter role is below. A member placed into this role will be able to perform INSERT, UPDATE, and DELETE commands on any table in the database.
exec sp_dbfixedrolepermission 'db_datawriter' DbFixedRole Permission db_datawriter DELETE permission on any object db_datawriter INSERT permission on any object db_datawriter UPDATE permission on any object
If an existing database role already has the required permission set for a user, the best solution is to use the fixed database role instead of creating a new role. In WDA, click on the database where you want to edit roles, then click on the Database - > Roles link in the left hand pane. Finally, click the edit button in the row for the role you want to edit, which will bring you to the following screen where you can check off available users to add to the role.
To add a user into a role using the system stored procedures, use the sp_addrolemember stored procedure, passing the database role and the user name.
exec sp_addrolemember 'db_owner', 'Donna'
You can view a list of the roles with and their members using the sp_helprolemember procedure. Removing a user from a role is accomplished with the sp_droprolemember procedure, shown below.
exec sp_droprolemember 'db_owner', 'Donna'
The Public Role
Every database also contains a fixed role named public. Every user in a database is a member of the public role. You cannot remove the public role or remove users from the public role. The permissions assigned to the public role are the default permissions for the database. We will be taking a closer look at roles later in the article.
Creating Roles
The current release of Web Data Administrator does not allow you to add custom roles. There are, however, system stored procedures available. You can create a custom role using the sp_addrole stored procedure and passing the role name:
exec sp_addrole 'customrole'
You can only create roles at the database level. The only server roles available are the fixed server roles. Adding a user to a custom role uses the same sp_addrolemember procedure we have seen earlier.
exec sp_addrolemember 'customrole', 'accounting'
To remove a role, execute the sp_droprole stored procedure passing the name of the role as a parameter. You need to remove all members of a role before the procedure returns success. Remove a member from a role with the sp_droprolemember stored procedure.
exec sp_droprolemember 'customrole', 'accounting' exec sp_droprole 'customrole'
With all users and roles in place we can look at assinging specific permissions to users and roles.
Applying Permissions
You can grant, deny, or revoke permissions to database users and database roles. Permissions are assignable at the database object level (individual tables and stored procedures, for example) and also to certain statements like CREATE TABLE. The Web Data Administrator interface does not currently provide an interface to these methods, so we will be examining the stored procedures available.
The ability to adjust the permissions is itself restricted by permissions. Only users in the sysadmin, db_owner, or db_securityadmin roles can set permissions. The owner of a database object (the creator) can always adjust permissions on objects they own.
In the following examples we will change the permissions to SELECT data from the Employees table of the Northwind database.
Revoke A Permission
The public role has the ability to SELECT from the Employees table. As mentioned earlier, every database user is a member of the public group. Thus any user has the ability to SELECT from the Employees table. We can remove the permission with the REVOKE command.
REVOKE SELECT ON Employees TO public
The revoke syntax for a database object requires you to specify the privilege (you can also specifiy multiple priviledges as we will see later in the article), the object name, and the user or role you are revoking from.
After the REVOKE statement executes, a user who is only in the public role and trys to select data will see this error:
Server: Msg 229, Level 14, State 5, Line 1 SELECT permission denied on object 'Employees', database 'Northwind', owner 'dbo'.
Grant A Permission
Now let's give our accouting group permission to select from the Employee table. Remember over the course of these two article we first gave a Windows group, the "accounting" group, a login to the database server. The group consists of three people: Alex, Geddy, and Donna. We then mapped the accounting group to a Northwind database user with the same name (accounting) using sp_grantdbaccess. Now, when we grant permissions to this user, all thee users will be able to perform a SELECT.
GRANT SELECT ON Employees TO accounting
Deny A Permission
Deny will explicitly prohibit a permission from a user or role. Since a user can be in multiple groups, they may pick up different permission grants from these different roles. The only way to ensure a user never has a permission to the explicity DENY the permission. For example, we will give Alex from the accounting group his own user account in Northwind:
exec sp_grantdbaccess 'machine\Alex', 'alex'
When Alex logs in he will access Northwind as alex instead of accounting. Remember MSDE is as selective as possible when mapping a login to a database user. Now we will explicitly deny Alex SELECT access to the Employees table.
DENY SELECT On Employees TO Alex
Alex is a member of the accounting group, and the accounting group has permissions to SELECT from Employees, but since we have explicitly denied this permission to Alex he will not have the permission. A DENY always takes precedence over any permissions given with a GRANT.
Use a DENY instead of a REVOKE to ensure the permission is never inherited from a user's membership in another role or group. For example, suppose we added Geddy as a user.
exec sp_grantdbaccess 'machine\Geddy'
And revoke his permission to the Employee table.
REVOKE SELECT ON Employees TO Geddy
Geddy will still be able to SELECT from the Employee table because he is a member of the accounting group that has this permission. Hopefully this will give you a clearer picuture of the differences in DENY and REVOKE.
Object Permissions
For tables and views we can grant, deny, or revoke the following permissions: SELECT, INSERT, DELETE, UPDATE, and REFERENCES.
You can place more than one permission in a GRANT, DENY, or REVOKE statement. For example, if we wanted to allow accounting to retrieve, insert, and update rows, we could use the following.
GRANT SELECT, INSERT, UPDATE ON Employees To accounting
For SELECT and UPDATE you can also include a column list. If we wanted to remove Alex's ability to see the BirthDate and HireDate columns we could use:
DENY SELECT (BirthDate, HireDate) ON Employees TO Alex
We are using a DENY statement instead of a REVOKE to make sure Alex does not get permissions to view these columns from his membership in the accounting group. If he tries to do a SELECT * on the table he will see this error:.
Server: Msg 230, Level 14, State 1, Line 1 SELECT permission denied on column 'HireDate' of object 'Employees', database 'Northwind', owner 'dbo'. Server: Msg 230, Level 14, State 1, Line 1 SELECT permission denied on column 'BirthDate' of object 'Employees', database 'Northwind', owner 'dbo'.
The REFERENCES permission controls who can add foreign key constraints to a table. Foreign key constraints have an impact on INSERT, UPDATE and DELETE logic for a table, as well as performance implications.
Stored procedures have one permission to regulate: EXECUTE. Let's revoke the ability for the public role to execute the CustOrderHistory stored procedure with the following:
REVOKE EXECUTE ON CustOrderHist TO public
There is a shortcut to modify all permissions at once - the ALL keyword.
REVOKE ALL ON Employees To public
Statement Permissions
An admin can GRANT, DENY, and REVOKE permissions to the following statements: CREATE DATABASE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW, BACKUP DATABASE, BACKUP LOG.
The default is that only sysadmin, db_owner, and ddl_admin roles have the ability to create a table. We can give Alex the permission to create a table with the following command:
GRANT CREATE TABLE TO Alex
Likewise, we can grant, deny, or revoke all statement permissions with the ALL keyword.
REVOKE ALL TO Alex
Some statements, for example, DROP TABLE and ALTER TABLE, are implicitly given to the owner of a database object. If Alex creates a table in Northwind, he will be able to ALTER and DROP it (as will sysadmin and dbo roles).
Viewing Permissions
View the permissions in a database using sp_helprotect. Sample output is below
exec sp_helprotect Owner Object Grantee Grantor ProtectType Action Column dbo Alphabetical list of products public dbo Grant Delete dbo Alphabetical list of products public dbo Grant Insert dbo Alphabetical list of products public dbo Grant Select (All) dbo Alphabetical list of products public dbo Grant Update (All) dbo Categories public dbo Grant Delete dbo Categories public dbo Grant Insert dbo Categories public dbo Grant References (All+New) dbo Categories public dbo Grant Select (All+New) dbo Categories public dbo Grant Update (All+New) dbo CustOrderHist public dbo GrantExecute ….continued….
You can view the permissions assigned to a specific user or role by passing the name to the stored procedure. You can view the permissions assigned to a specific object by passing the object name to the stored procedure.
exec sp_helprotect 'Orders' Owner Object Grantee Grantor ProtectType Action Column dbo Orders public dbo Grant Delete dbo Orders public dbo Grant Insert dbo Orders public dbo Grant References (All+New) dbo Orders public dbo Grant Select (All+New) dbo Orders public dbo Grant Update (All+New)
The sp_helprotect stored procedure only provides output for explicit permissions. The permissions given to fixed roles (such as sysadmin or db_owner) are implicit, unchangeable, and not listed in the output.
Summary
In this series of articles we have presented the basic security concepts for MSDE 2000 and SQL Server 2000. We have seen how to take advantage of the Web Data Administrator where possible to manage the security settings of our database server. We have covered Windows versus mixed-mode authentication, mapping logins to database users, creating custom roles, and using grant, revoke, and deny to set permissions.
By Scott Allen.
Subscribe
Twitter
Search
About
Learn C#