BUG: SQL Enterprise Manager May Show Incorrect Permissions

• Microsoft SQL Server, versions 6.0 and 6.5

SYMPTOMS

The SQL Enterprise Manager (SEM) may display incorrect permissions if objects are created under the same name but by different owners. The following procedure demonstrates the problem:

1. Create user1, user2, and user3.

2. Grant the appropriate permissions to these three users so that they can all select from authors.

3. Have user1 log on and create the following view:

         CREATE VIEW view_authors_names
         AS
         SELECT au_fname, au_lname
         FROM authors
   

4. Have user2 log on and create the following view:

         CREATE VIEW view_authors_names
         AS
         SELECT au_fname, au_lname
         FROM authors
   

5. Grant user3 both select permissions for user1.view_authors_names and user2.view_authors_names.

6. Go to SEM and select one of objects in view from the pubs database.

7. Right-click the mouse and look at the permission by user; you should see the incorrect permissions result.

WORKAROUND

To work around this problem, use Transact-SQL instead. The following sample script illustrates how query permissions can be distinguished by a user's logon:

   USE databaseName
   GO

   SELECT object = (u.name + '.' + o.name),
          permission = spt.name
   FROM   sysprotects p,
          sysobjects o,
          sysusers u,
          master..spt_values spt
   WHERE  p.uid = user_id('user's login') AND
          p.id = o.id AND
          o.uid = u.uid AND
          spt.number = p.action AND
          spt.name <> NULL
   GO

STATUS