BUG: SQL Enterprise Manager May Show Incorrect Permissions (166203)

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.

You may observe similar incorrect behavior with different objects, such as tables. For example, two tables created under the same name but different users also have the incorrect permission problem.

   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