Lesson 1: Granting Database-Specific Permissions | MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228

3 4

Regardless of the authentication method by which a user receives access to SQL Server 2000, the user must have permissions to perform any activities within a user database. In the previous chapter you learned how to grant user permissions using server roles and fixed database roles. In this lesson, you learn about the difference between the use of fixed roles and the granting of specific statement and object permissions to users, groups, and user-defined database roles, including how to grant statement and object permissions using SQL Server Enterprise Manager and Transact-SQL statements and system stored procedures.

After this lesson, you will be able to

Describe each type of database-specific permissions
Grant, revoke, and deny statement permissions
Grant, revoke, and deny object permissions
Determine and view effective permissions

Estimated lesson time: 45 minutes

Implementing Permissions

Database users need permission to work with data, execute stored procedures, create database objects, and perform administration tasks. Users acquire full or limited permissions within a database in a variety of ways.

Membership in the sysadmin server role
Individual ownership of the database
Ownership of a database object individually, through a database role, or through a Windows 2000 (or Windows NT 4.0) group
Membership in a fixed database role
The granting of specific permissions individually, through a database role, or through a Windows 2000 (or Windows NT 4.0) group
The inheritance of public role permissions as a permitted database user
The inheritance of guest user permissions as a user with no permitted database access

Inherited Permissions

The database owner, members of the sysadmin server role, and members of the db_owner fixed database role inherit full permission to perform any activity within a database. In addition, members of a fixed database role inherit broad permissions to perform specific types of tasks. For example, members of the db_securityadmin fixed database role can run GRANT, REVOKE, or DENY statements with respect to all database objects, but have no permission to execute any CREATE or BACKUP statement. Likewise, members of the db_ddladmin fixed database role can run any CREATE or BACKUP statement, but have no permission to run the GRANT, REVOKE, or DENY statements. Furthermore, members of the db_datareader role can read data from any table or view within the database, and members of the db_datawriter role can write and modify data within all tables and views in the database.

An object owner (you must grant a user the right to create an object) inherits all permissions associated with the object, including the right to grant other users permissions to use the object. Members of the sysadmin server role, the db_ddladmin fixed database role, and the db_securityadmin fixed database role can change ownership of any object in the database (and revoke all permissions on the object).

Permission Actions and Conflicts

The broad scope of permissions contained in server roles and fixed database roles, as well as specifically limited statement and object permissions, can be granted (or denied) to Windows groups and user-defined roles (in addition to individual users). Permissions granted to a group or a role apply to all members of the group or role. The effective permissions of a user that is a member of multiple groups or roles are the cumulative permissions of the roles and groups.

You can also revoke or deny permission to specific roles, groups, or users. As in Windows 2000, the denial of permission takes precedence over all other permissions. For example, if you grant permission to a user to view a table, but that user is a member of a group or role to which you have denied permission, the user is denied permission to view the table.

Note

The grant or denial of permissions to a Windows user or group has no effect when a connection is established using a SQL Server 2000 security account. SQL Server 2000 has no knowledge of that Windows user, only of the SQL Server 2000 security account used and its permissions.

Practice: Creating and Testing Permission Conflicts

In this practice you create a permission conflict and then test it.

To create and test permission conflicts

Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
SQL Server Enterprise Manager appears displaying the Microsoft SQL Servers and the Event Viewer (Local) console trees in the console root.
In the console tree, expand the Microsoft SQL Servers container, expand the SQL Server Group container, expand the default instance, expand the Databases Container, expand the SSEMDB database container, and then click Users.
In the details pane, double-click Joe.
The Database User Properties – Joe dialog box appears.
In the Database Role Membership group box, select the Db_owner check box and then click the Apply button.
Click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.
The Connect To SQL Server dialog box appears.
Click the SQL Server Authentication option button.
In the Login Name text box, type Joe.
In the Password text box, type password and then click OK.
Notice that you are connected as Joe to the master database.
On the toolbar, change the current database to SSEMDB in the database drop-down list.
In the query pane, type SELECT * FROM Customer.
On the toolbar, click the Execute Query button.
Notice that Joe can view all rows in the Customer table.
Switch to SQL Server Enterprise Manager.
In the Database Role Membership group box, select the Db_denydatareader check box and then click the Apply button.
Do not close the Database User Properties – Joe dialog box. You will use this dialog box in the next practice.
Switch to SQL Query Analyzer.
On the toolbar, click the Execute Query button to re-execute the SELECT * FROM Customer query.
Notice that Joe no longer has permission to view any rows in the Customer table. There is a permission conflict, and the DENY permission supersedes all grants of permissions.
Do not close SQL Server Enterprise Manager or SQL Query Analyzer.

Managing Statement Permissions

Statement permissions are permissions to run particular Transact-SQL statements that create databases and database objects (such as tables, views, and stored procedures) in that database. Table 11.1 describes the permissions associated with these statements. Permissions with respect to each of these statements can be granted, denied, or revoked.

Creating Objects and Chain of Ownership Issues

When a user creates a database object (such as a table or view), the user is the owner of the object (unless another user, group, or role is specified as the owner). Allowing a user to own objects can be useful during development of a database, but it causes problems in production and so you should avoid it for several reasons (as covered in the rest of this section). It is good practice to have all objects in a production database owned by the dbo role.

When a user references an object in a script, the object can be qualified with the name of the object owner or it can be unqualified. If the object is unqualified as to the owner, SQL Server 2000 looks for the object in the database as either owned by the user executing the script or owned by the dbo role. If it is not found as owned by either, an error is returned.

 SELECT * FROM Customer

The preceding example will return the data within the Customer table provided that the table is either owned by the dbo role or owned by the user executing the script.

 SELECT * FROM Joe.Customer

The preceding example will return the data within the Customer table owned by Joe. If there is another Customer table in the database owned by the dbo role, it will not be returned. Having multiple tables with the same name and having to specify a name other than dbo can cause confusion.

In addition, views and stored procedures can be created on tables. When a user attempts to select information through a view or procedure, SQL Server 2000 must verify whether the user is permitted to view the data. If the view or procedure is owned by one user and the underlying table owned by another, SQL Server 2000 must check permissions on each object in the chain. As the chain of ownership lengthens, this can affect performance. But perhaps more importantly, it can be very confusing for an administrator to trace and debug security issues.

Table 11.1 Statement Permissions

Transact-SQL Statement	Permission to Execute the Transact-SQL Statement
CREATE DATABASE	Inherited by members of the sysadmin and dbcreator server roles. Although the sysadmin and securityadmin server roles can grant permission directly to security accounts to run this statement, generally the security accounts use the dbcreator server role if the system administrator delegates permission. This permission exists only in the master database.
BACKUP DATABASE BACKUP LOG	Inherited by members of the sysadmin server role and the db_owner and db_backupoperator fixed database roles. Although you can grant permission to run these statements directly to security accounts, generally you will use the db_backupoperator fixed database role.
CREATE TABLE CREATE VIEW CREATE PROCEDURE CREATE DEFAULT CREATE RULE CREATE FUNCTION	Inherited by members of the sysadmin server role and the db_owner and db_ddladmin fixed database roles. Permission to create these objects is sometimes granted directly to programmers (or to a programmers group or role) during development. By default, objects are owned by the creator of the object (although objects created by members of the sysadmin server role are owned by the dbo role). Members of the db_owner or db_ddladmin fixed database roles can designate the dbo role as the owner of an object they create. In addition, members of the sysadmin server role or the db_owner or db_ddladmin fixed database role can designate any user as the owner of an object they create. However, users who are not members of one of these roles cannot designate another user or the dbo role as the owner of an object they create.
CREATE TRIGGER	Inherited by the table owner on which the trigger is defined, members of the sysadmin server role, and the db_owner and db_ddladmin fixed database roles. These members cannot grant permission to run this statement to other security accounts.

When members of the db_owner and db_ddladmin fixed database roles create a database object, it is good practice to specify the dbo role as the owner of the object. If no owner is specified, ownership will default to the Windows user or SQL Server 2000 login account that created the object.

 CREATE TABLE Northwind.dbo.CustomerTable   ( CustID nchar (5) , CustomerName nvarchar (40) )

The preceding example shows the creation of a table granting the ownership to the dbo role. Only members of the sysadmin server role and the db_owner or db_ddladmin fixed database roles can successfully execute this statement.

Changing Object Ownership

If a database object exists with an owner other than the dbo role, you might need to change its ownership. A member of the db_owner, db_ddladmin, or db_securityadmin fixed database role, or a member of the sysadmin server role can change the ownership of any object in the database by running the sp_changeobjectowner system stored procedure.

 sp_changeobjectowner 'SelfPacedSQL\Bill.Customer' , 'dbo'

The preceding example changes the ownership of the Customer table from SelfPacedSQL\Bill to the dbo role.

Note

Changing the owner of an object removes all existing permissions on the object. If you need to retain the permissions, script out the existing permissions before running the sp_changeobjectowner system stored procedure. You can then reapply the permissions by modifying the object owner in the saved script and then running the saved permissions script.

Using SQL Server Enterprise Manager to Grant, Deny, or Revoke Statement Permissions

SQL Server Enterprise Manager provides a simple graphical interface for viewing existing statement permissions and granting, denying, and revoking statement permissions. To view statement permissions within a database, right-click the database and then click Properties. On the Permissions tab in the Properties dialog box, you can view, grant, revoke, or deny permissions. See Figure 11.1.

Notice that each permitted user and user-defined database role is displayed along with the specific statement permissions granted that user or role, if any. A green check means granted, a red X means denied, and a cleared check box means revoked or neutral. Remember that permissions are generally cumulative, but that a DENY permission takes precedence. For example, in this figure the role public has been denied permission to create a table. Therefore, the login Joe will not be able to create a table although he has been specifically granted that permission. However, a denial of statement permissions has no effect on a member of the sysadmin server role (which includes the sa account). Finally, the statement permissions displayed are only those statement permissions directly granted. Statement permissions inherited by a user account by virtue of membership in a server role or a fixed database role are not displayed here.

figure 11.1 - viewing and changing statement permissions.

Figure 11.1

Viewing and changing statement permissions.

Using Transact-SQL to Grant, Deny, or Revoke Statement Permissions

You can use the GRANT, DENY, and REVOKE statements to manage statement permissions using Transact-SQL scripts. These statements can be used to grant, deny, and revoke permission to run specific statements. When these statements are used with the ALL keyword, permissions on all statements and objects are affected.

 GRANT CREATE TABLE TO Joe, SalesManagers, [SelfPacedSQL\SQLServerAdmins]

The preceding example grants the CREATE TABLE statement permission to Joe (a SQL Server login), SalesManagers (a user-defined database role), and SelfPacedSQL\SQLServerAdmins (a Windows group).

Note

Specify BUILTIN rather than the domain or local computer name when referencing a Windows local built-in group, such as BUILTIN\Backup Operators.

 DENY CREATE TABLE TO Joe

The preceding example denies the CREATE TABLE statement permission to Joe. To remove a DENY permission, use the REVOKE or the GRANT statement.

 REVOKE ALL FROM Joe

The preceding example revokes all grants of permissions to Joe and revokes all denials of permissions to Joe. This includes statement and object permissions.

Viewing Statement Permissions Using Transact-SQL

You can view existing statement permissions within a database using the sp_helprotect system stored procedure. All database users have permission to execute this system stored procedure.

 EXEC sp_helprotect NULL, NULL, NULL, 's'

The preceding example lists all statement permissions in the current database.

Practice: Granting and Testing Statement Permissions

In this practice you grant and test statement permissions.

To grant and test statement permissions

Switch to SQL Query Analyzer.
Verify that you are connected as Joe.
On the toolbar, click the Load SQL Script button.
A SQL Query Analyzer dialog box appears asking if you want to save the changes to the previous script.
Click the No button.
The Open Query File dialog box appears.
Open CreateTestTable1.sql in the C:\SelfPacedSQL\CH_11 folder.
A Transact-SQL script appears that will create a new table called TestTable1 and add a single row of data to the newly created table. Notice that ownership of the table is not qualified as to owner. Finally, the script queries the newly created table.
On the toolbar, click the Execute Query button.
Notice that the table is created successfully and one row of data added. Joe can create the table because he is a member of the db_owner role. However, Joe cannot view the data because he is a member of the db_denydatareader fixed database role.
Switch to SQL Server Enterprise Manager.
Notice that the Database User Properties – Joe dialog box is still open from the last practice.
In the Database User Properties – Joe dialog box, clear the Db_denydatareader check box and then click the Apply button.
Switch to SQL Query Analyzer.
In the query pane, highlight SELECT * FROM TestTable1 and then click the Execute Query button on the toolbar.
Notice that Joe can now execute the query successfully.
On the File menu, click Connect.
The Connect To SQL Server dialog box appears.
Click the SQL Server Authentication option button.
In the Login Name text box, type sa and then click OK.
Notice that you can connect as sa with no password. In a production environment, you should never enable Mixed Mode authentication and leave the sa password blank.
On the toolbar, change the current database to SSEMDB in the database drop-down list.
In the query pane, type SELECT * FROM TestTable1 and then click the Execute Query button on the toolbar.
Notice that the TestTable1 object is not found when the sa user account does not qualify TestTable1 as to the owner. The reason is that SQL Server 2000 only looks for this object as owned either by the sa user or by the dbo role. Because the table is owned by Joe, it is not found.
In the query pane, change the query to read SELECT * FROM Joe.TestTable1 and then click the Execute Query button on the toolbar.

Notice that the contents of the TestTable owned by Joe are displayed.

Switch to Joe's connection in SQL Query Analyzer.
Change the CREATE TABLE statement to qualify the owner as the dbo role (the statement should now begin CREATE TABLE.dbo.TestTable1. . .), and then click the Execute Query button on the toolbar.
Notice that the table is created successfully and one row of data added. However, notice that the data was added to Joe.TestTable1, not to dbo.TestTable1.
Switch to SQL Server Enterprise Manager.
In the Database User Properties – Joe dialog box, clear the Db_owner check box and then click OK.
In the console tree, right-click the SSEMDB database container and then click Properties.
The SSEMDB Properties dialog box appears, with the General tab selected.
Click the Permissions tab.
For the user Joe, select the Create Table, Create View, and Create SP check boxes and then click OK.
Switch to SQL Query Analyzer.
Verify that you are connected as Joe.
On the toolbar, click the Load SQL Script button.
A SQL Query Analyzer dialog box appears asking if you want to save the changes to the previous script.
Click the No button.
The Open Query File dialog box appears.
Open CreateTestTable2_Proc2View2.sql in the C:\SelfPacedSQL\CH_11 folder.
A Transact-SQL script appears that will create a new table called TestTable2, add a single row of data to the newly created table, create a stored procedure that displays three columns from the table, and create a view that displays two columns from the table. Notice that ownership of each object created is not qualified as to owner. Finally, the script queries the newly created table.
On the toolbar, click the Execute Query button.
Notice that the table, procedure, and view are all created successfully. Joe has explicit permission to create tables, stored procedures, and views. Joe can display the contents of the table and the view and execute the stored procedure because he is the owner of these objects.
Switch to SQL Server Enterprise Manager.
In the console tree, under the SSEMDB container, click the Stored Procedures container.
Right-click Stored Procedures and then click Refresh.
Notice, in the details pane, that Joe is the owner of the TestTable2_Procedure2 stored procedure.
Switch to SQL Query Analyzer.
Verify that you are connected as Joe.
Change the CREATE TABLE statement to qualify the owner as the dbo role (the statement should now begin CREATE TABLE.dbo.TestTable2. . .).
Highlight the entire CREATE TABLE statement (but no other statements in the script), and then click the Execute Query button on the toolbar.
Notice that Joe cannot create the table specifying the dbo role as the owner. The CREATE TABLE permission does not grant this privilege (unlike membership in the db_owner fixed database role).
Switch to the sa connection in SQL Query Analyzer.
On the toolbar, click the Load SQL Script button.
A SQL Query Analyzer dialog box appears asking if you want to save the changes to the previous script.
Click the No button.
The Open Query File dialog box appears.
Open TestTable2_ChangeOwner.sql in the C:\SelfPacedSQL\CH_11 folder.
A Transact-SQL script appears that will change the ownership of TestTable2, TestTable2_Procedure2, and TestTable2View2 to the dbo role. It will then test the ownership change.
On the toolbar, click the Execute Query button.
Notice that the ownership of each of these three database objects is successfully changed.
Close all connections in SQL Query Analyzer. Do not save any changes.
On the File menu, click Connect.
The Connect To SQL Server dialog box appears.
Click the SQL Server Authentication option button.
In the Login Name text box, type Joe.
In the Password text box, type password and then click OK.
Notice that you are connected as Joe to the master database.
On the toolbar, change the current database to SSEMDB in the database drop-down list.
In the query pane, type SELECT * FROM TestTable2 and then click the Execute Query button on the toolbar.

Notice that Joe cannot view any rows in the TestTable2 table. The dbo role now owns the table. Joe has no SELECT permissions on any objects of which he is not the owner.

On the toolbar, click the Clear Window button.
In the query pane, type EXEC sp_helprotect NULL, NULL, NULL, 's' and then click the Execute Query button on the toolbar.
Notice that the three statement permissions granted to Joe are displayed. Any permissions granted by virtue of membership in a fixed database role are not displayed.
Close all open connections in SQL Query Analyzer, but do not close SQL Query Analyzer.

Managing Object Permissions

Object permissions are permissions to perform actions with respect to tables, views, functions, and stored procedures. Table 11.2 describes the type of object permissions associated with database objects. Permission with respect to each of these statements can be granted, denied, or revoked by members of the sysadmin server role or the db_owner and db_securityadmin fixed database roles (or by a database object owner).

Table 11.2 Types of Object Permissions Associated with Database Objects

Database Object Permission	Permission on the Database Object
SELECT	Permission to view information in a table, view, column, or certain user-defined functions. Inherited by members of sysadmin server role and the db_owner and db_datareader fixed database roles. Denied to all members of the db_denydatareader fixed database role.
INSERT	Permission to add new data to a table or view. Inherited by members of sysadmin server role and the db_owner and db_datawriter fixed database roles. Denied to all members of the db_denydatawriter fixed database role.
UPDATE	Permission to update data in a table, column, or view. Inherited by members of sysadmin server role and the db_owner and db_datawriter fixed database roles. Denied to all members of the db_denydatawriter fixed database role.
DELETE	Permission to delete data from a table or view. Inherited by members of sysadmin server role and the db_owner and db_datawriter fixed database roles. Denied to all members of the db_denydatawriter fixed database role.
EXECUTE	Permission to run stored procedures and user-defined functions. Inherited by members of sysadmin server role and the db_owner fixed database roles.
REFERENCES	Permission to refer to a table with a FOREIGN KEY constraint without having SELECT permissions on the table. Inherited by members of sysadmin server role and the db_owner and db_datareader fixed database roles. Denied to all members of the db_denydatareader fixed database role.

Notice that you can use the db_datareader and db_denydatareader fixed database roles to grant or deny the SELECT and REFERENCES object permissions with respect to all objects in the database. You can also use the db_datawriter and db_denydatawriter fixed database roles to grant or deny the INSERT, UPDATE, and DELETE object permissions with respect to all objects in the database. To grant the EXECUTE object permission to a user, you generally must specifically grant that permission (to a user, group, or role) because no fixed database role (other than db_owner fixed database role) grants that permission. In addition, if you need to grant or deny object permissions on a subset of the database objects in a database, you must specifically grant or deny those object permissions. You can grant, deny, or revoke them with respect to a user-defined database role, Windows group, SQL Server login, or Windows user.

Finally, a user or role can be denied permission to view or update a table directly, but may be given permissions on the table through a view or a stored procedure. For example, a view can be created that displays only certain columns or rows in a table. A user can then be permitted to update data through the view (such as certain employee information without seeing all employee information). Or a stored procedure can be created that displays all employee names, but no other information.

Using SQL Server Enterprise Manager

SQL Server Enterprise Manager provides a simple graphical interface for viewing existing object permissions and granting, denying, and revoking object permissions. To view object permissions for a table, view, or stored procedure, right-click the object in the details pane and then click Properties to display the Properties dialog box. Click the Permissions button, which displays the Object Properties dialog box, to view, grant, revoke, or deny permissions. See Figure 11.2.

figure 11.2 - viewing and changing object permissions.

Figure 11.2

Viewing and changing object permissions.

As in the graphical interface for statement permissions, each permitted user and user-defined database role is displayed along with the specific object permissions granted that user or role. Notice that you can choose to list only the users or roles that actually have permissions on this object. See Figure 11.3.

You can also click a particular user or role and then click the Columns button, which displays the Column Permissions dialog box, to view or control permissions at the column level. For example, you might want to restrict SELECT or UPDATE permissions on a particular column in a table to which you have granted SELECT or UPDATE permissions. See Figure 11.4.

figure 11.3 - viewing only users or roles with permissions on the particular object.

Figure 11.3

Viewing only users or roles with permissions on the particular object.

figure 11.4 - restricting permissions on a specific column.

Figure 11.4

Restricting permissions on a specific column.

In addition, from the Object Properties dialog box for any table, view, or stored procedure, you can change to any other object by clicking that object in the Object drop-down list. See Figure 11.5.

Notice that a stored procedure is now displayed in Figure 11.5. The only permission available for a stored procedure is EXECUTE.

In addition to viewing object permissions from the object perspective, you can also view and manage object permissions from the user or role perspective. In the details pane of Enterprise Manager, right-click a user-defined database role or a permitted user, click Properties, and then click the Permissions button. See Figure 11.6.

figure 11.5 - changing the object for which you are viewing permissions.

Figure 11.5

Changing the object for which you are viewing permissions.

figure 11.6 - viewing object permissions from the user or role perspective.

Figure 11.6

Viewing object permissions from the user or role perspective.

In Figure 11.6, notice that you can either list only the objects with permissions for this role or list all objects for which you can configure permissions. This interface eases the task of viewing and configuring permissions from the perspective of a role or a user.

Note

After you have configured permissions with SQL Server Enterprise Manager for database objects, you should use the scripting capability of SQL Server Enterprise Manager to generate Transact-SQL scripts to enable you to reconstruct these permissions should the need arise.

Using Transact-SQL to Grant, Deny, or Revoke Object Permissions

You can use the GRANT, DENY, and REVOKE statements to manage object permissions using Transact-SQL scripts.

 GRANT SELECT ON Customer TO Joe, SalesUsers, [SelfPacedSQL\SQLServerUsers]

The preceding example grants the SELECT object permission on the Customer table to Joe (a SQL Server login), SalesUsers (a user-defined database role), and SelfPacedSQL\SQLServerUsers (a Windows group).

 DENY INSERT, UPDATE, DELETE TO Joe

The preceding example denies the INSERT, UPDATE, and DELETE object permissions to Joe.

When granting object permissions using Transact-SQL statements, you also have the option to use the WITH GRANT OPTION clause. Through the use of this option, you can grant a user, role, or group specified object permissions and also grant that user the right to grant those same permissions to any other permitted user, role, or group in the database.

 GRANT SELECT ON Customer TO SalesManagers WITH GRANT OPTION

The preceding example grants the SELECT object permission on the Customer table to SalesManagers (a user-defined database role). Any member of the SalesManagers role can grant the SELECT object permission on the Customer table. If the WITH GRANT OPTION clause is granted to a group, a user from this group attempting to grant this permission to another user, group, or role with the GRANT statement must use the AS option.

 GRANT SELECT ON Customer TO Joe AS SalesManagers

The preceding example grants the SELECT object permission on the Customer table to Joe. The member of the SalesManagers role executing this GRANT statement references his or her membership in the SalesUsers role by using the AS option to validate his or her permission to exercise the GRANT permission.

To revoke the WITH GRANT OPTION clause without revoking the underlying permissions, use the GRANT OPTION FOR clause.

 REVOKE GRANT OPTION FOR ON Customer FROM Joe AS SalesManagers

The preceding example revokes the WITH GRANT OPTION clause from Joe without revoking Joe's permission on the Customer table. To deny or revoke object permissions initially granted using the WITH GRANT OPTION clause, specify the CASCADE clause to also revoke or deny permissions that were granted from the original account.

 REVOKE GRANT OPTION FOR ON Customer FROM Joe CASCADE AS SalesManagers

The preceding example revokes the WITH GRANT OPTION clause from Joe and also revokes all permissions granted by Joe to other users.

Note

Use the WITH GRANT OPTION clause with extreme caution. The only future control you have over the security accounts that receive permission is to revoke or deny object permissions after the fact (and possibly after damage has occurred).

Viewing Permissions

You can view existing object permissions within a database using the sp_helprotect system stored procedure. All database users have permission to execute this system stored procedure.

 EXEC sp_helprotect 'Customer'

The preceding example lists all object permissions for the Customer table in the current database.

 EXEC sp_helprotect NULL , 'Joe'

The preceding example lists all statement and object permissions that Joe has in the current database.

 EXEC sp_helprotect NULL , NULL , 'SalesManagers'

The preceding example lists all object permissions granted by members of the SalesManagers role in the current database.

Practice: Granting and Testing Object Permissions

In this practice you grant and test object permissions.

To grant and test object permissions

Switch to SQL Query Analyzer.
On the File menu, click Connect.
The Connect To SQL Server dialog box appears.
Click the SQL Server Authentication option button.
In the Login Name text box, type sa and then click OK.
Notice that you are connected as sa to the master database.
On the toolbar, change the current database to SSEMDB in the database drop-down list.
On the toolbar, click the Load SQL Script button.
The Open Query File dialog box appears.
Open View_Proc.sql in the C:\SelfPacedSQL\CH_11 folder.
A Transact-SQL script appears that will create a new stored procedure called Customer_Procedure, grant EXECUTE permissions on this procedure to the guest group, create a new view called Customer_View1, and grant SELECT permissions on this view to the public group.
On the toolbar, click the Execute Query button.
Notice that the Customer_Procedure was created and EXECUTE permissions were granted to the guest user role. Notice also that the Customer_view was created and SELECT permissions granted to the public role.
On the File menu, click Connect.
The Connect To SQL Server dialog box appears.
Click the SQL Server Authentication option button.
In the Login Name text box, type Ana.
In the Password text box, type password and then click OK.
Notice that you are connected as Ana to the master database.
On the toolbar, verify that Ana cannot change the current database to SSEMDB in the database drop-down list.
Ana is not a user in the SSEMDB database.
In the query pane, type EXEC SSEMDB.dbo.Customer_Procedure and then click the Execute Query button on the toolbar.
Notice that Ana cannot execute the Customer_Procedure in the SSEMDB database, even though she is not a user in the SSEMDB database and EXECUTE permissions have been granted to the guest user account.
Switch to the sa connection in SQL Query Analyzer.
On the toolbar, click the Clear Window button.
In the query pane, type sp_helpuser 'guest' and then click the Execute Query button on the toolbar.
Notice that there is no guest user account in the SSEMDB database. This is why Ana cannot access the SSEMDB database.
On the toolbar, click the Clear Window button.
Type sp_grantdbaccess 'guest' and then click the Execute Query button on the toolbar.
Notice that the guest user role has been granted user access to the SSEMDB database.
Switch to the Ana connection in SQL Query Analyzer.
Re-execute the EXEC SSEMDB.dbo.Customer_Procedure query.
Notice that Ana is now able to execute the Customer_Procedure stored procedure, which displays three of the columns from the Customer table.
22. On a new line, type SELECT * FROM SSEMDB.dbo.Customer_View1.
Highlight this new query and then click the Execute Query button on the toolbar.
Notice that Ana is able to select information from Customer_View1. She is a guest user and SELECT permissions have been granted to the public role, of which the guest user is a member.
Switch to the sa connection in SQL Query Analyzer.
On the toolbar, click the Clear Window button.
Type sp_grantdbaccess 'Ana' and then click the Execute Query button on the toolbar.
Notice that Ana has been granted user access to the SSEMDB database. Switch to the Ana connection in SQL Query Analyzer.
On the toolbar, click the Execute Query button to re-execute both queries.
Notice that Ana is no longer able to execute the Customer_Procedure stored procedure, although she is able to select from the Customer_View1 view. She is now a user in the SSEMDB database. EXECUTE permissions have only been granted to the guest user account, not to the public role.
Switch to SQL Server Enterprise Manager.
In the SSEMDB database container, right-click Users and then click Refresh.
In the SSEMDB datasbase container, right-click Roles and then click New Database Role.
The Database Role Properties – New Role dialog box appears.
In the Name text box, type SSEMDB_CustomRole.
Click OK.
The SSEMDB_CustomRole appears in the details pane.
In the details pane, double-click SSEMDB_CustomRole.
The Database Role Properties – SSEMDB_CustomRole dialog box appears. Notice that no one is currently a member of this user-defined database role.
Click the Add button.
The Add Role Members dialog box appears.
Click Ana and then click OK.
Notice that Ana now appears as a member of the SSEMDB_CustomRole.
Click the Apply button.
Click the Permissions button.
The Database Role Properties – SSEMDB dialog box appears. Notice that this role has no current permissions.
For the object, Customer_View1, select the Select, Insert, Update, and Delete check boxes.
Notice that a green check mark appears indicating that this role will be granted SELECT, INSERT, UPDATE, and DELETE permissions on this object.
For the object, Customer_View1, select the Select, Insert, Update, and Delete check boxes again.
Notice that a red X appears indicating that this role will be denied SELECT, INSERT, UPDATE, and DELETE permissions on this object.
Click OK.
Switch to the Ana connection in SQL Query Analyzer.
On the toolbar, click the Execute Query button.
Notice that Ana can neither execute the stored procedure nor select from the view.
Switch to SQL Server Enterprise Manager.
In the Database Role Properties – SSEMDB_CustomRole dialog box, click Ana and then click the Remove button.
Click OK.
In the console tree, click the Stored Procedures container.
In the details pane, double-click Customer_Procedure.
The Stored Procedure Properties – Customer_Procedure dialog box appears.
Click the Permissions button.
The Object Properties – SSEMDB dialog box appears.
For the database user, Ana, select the Exec check box and then click OK.
In the Stored Procedure Properties – Customer_Procedure dialog box, click OK.
Switch to the Ana connection in SQL Query Analyzer.
On the toolbar, click the Execute Query button.
Notice that Ana can execute the stored procedure and select from the view.
Switch to SQL Server Enterprise Manager.
Click the Tables container.
In the details pane, double-click Customer.
The Table Properties – Customer dialog box appears.
Click the Permissions button.
The Object Properties – SSEMDB dialog box appears.
For the user, Ana, click the Select check box twice to deny SELECT permissions on the Customer database object and then click OK.
In the Table Properties – Customer dialog box, click OK.
Switch to the Ana connection in SQL Query Analyzer.
On the toolbar, click the Execute Query button.
Notice that Ana can still execute the stored procedure and select from the view, although she has been denied direct SELECT permissions on the underlying table.
Switch to SQL Server Enterprise Manager.
Click the Roles container.
In the details pane, double-click SSEMDB_CustomRole.
The Database Role Properties – SSEMDB_CustomRole dialog box appears.
Click the Add button.
The Add Role Members dialog box appears.
Select Ana, and then click OK.
Click the Permissions button.
The Database Role Properties – SSEMDB dialog box appears.
For the object Customer_Procedure, click the Exec check box twice to deny EXECUTE permissions and then click OK.
Click OK.
Switch to the Ana connection in SQL Query Analyzer.
On the toolbar, click the Execute Query button.
Notice that Ana can neither execute the stored procedure nor select from the view.
Switch to the sa connection in SQL Query Analyzer.
On the toolbar, click the Clear Window button.
Type sp_helprotect 'customer_procedure' and then click the Execute Query button on the toolbar.
Notice the object permissions on the customer_procedure database object.
On the toolbar, click the Clear Window button.
Type sp_helprotect 'customer_view1' and then click the Execute Query button on the toolbar.
Notice the object permissions on the customer_view1 database object.
Do not close SQL Server Enterprise Manager or the two connections in SQL Query Analyzer.

Lesson Summary

Statement and object permissions can be specifically granted to, revoked from, or denied to users, Windows groups, and user-defined roles. This capability is used to augment the permissions granted or denied through the use of fixed database roles. Generally, you will use this capability to extend or limit object permissions. However, you generally use fixed database roles (in addition to the sysadmin server role) for statement permissions. You can manage and view permissions using either SQL Server Enterprise Manager or Transact-SQL. The graphical interface makes the task quite simple, and Transact-SQL scripts allow you to apply security to many objects in a single script. When you use SQL Server Enterprise Manager, you should generate scripts to enable you to reconstruct or copy the security structure should the need arise.