PRB: SQL Server 7.0 Enterprise Manager Does not Provide Object and User Permissions Dialog Boxes Like SQL Server 6.5 (268858)

SYMPTOMS

In the Microsoft SQL Server 6.5 Enterprise Manager, the Object Permissions dialog box has a By User and a By Object tab. The user can select any object or user from the User/Group or Object drop-down list box, and then assign permissions in one convenient dialog box.

This functionality is not present in Microsoft SQL Server Enterprise Manager 7.0. To assign permissions by object, you must select each object individually by opening a separate dialog box for each object. However, you can assign permissions to multiple users on any one object at a time.

CAUSE

This behavior is by design.

In Microsoft SQL Server 7.0, you cannot change object selection in the Properties dialog box. This is because the dialog boxes are used by other Microsoft applications in Microsoft Management Console and adherence to behavioral consistency is important.

WORKAROUND

To assign permissions by using SQL Server Enterprise Manager for database objects in SQL Server 7.0, you can follow these steps:

Close the screen for the current object, and then right-click the new database object.
Click Properties, and then click Permissions.

This behavior applies to user permissions as well. Right-clicking any user name displays the permissions for all database objects for that particular user. However, assigning permissions to different database users on different database objects is not possible. The user must close the Properties dialog box for the current user, and then assign permissions to each user individually.

Additionally, you can use the GRANT and REVOKE Transact-SQL syntax in Query Analyzer to apply user permissions to objects.

STATUS

This has been fixed in Microsoft SQL Server 2000, so that the behavior is the same as Microsoft SQL Server 6.5.

In SQL Server 6.5, you can access the permissions to objects and users by right-clicking the user whose permissions you want to change; whereas, SQL Server 7.0 does not provide that functionality.