Microsoft KB Archive/166203

= BUG: SQL Enterprise Manager May Show Incorrect Permissions =

Article ID: 166203

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q166203



BUG #: 16745



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:

 Create user1, user2, and user3. Grant the appropriate permissions to these three users so that they can all select from authors.  Have user1 log on and create the following view:

CREATE VIEW view_authors_names AS     SELECT au_fname, au_lname FROM authors

  Have user2 log on and create the following view:

CREATE VIEW view_authors_names AS     SELECT au_fname, au_lname FROM authors

 Grant user3 both select permissions for user1.view_authors_names and user2.view_authors_names. Go to SEM and select one of objects in view from the pubs database. Right-click the mouse and look at the permission by user; you should see the incorrect permissions result.</li></ol>

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.

<div class="workaround_section">

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

<div class="status_section">

STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server versions 6.0 and 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

Keywords: kbbug kbpending kbusage KB166203

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.