Microsoft KB Archive/170446

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 10:05, 21 July 2020 by X010 (talk | contribs) (Text replacement - """ to """)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Knowledge Base


BUG: Error 4601 When DBO Assigns Permissions for a View

Article ID: 170446

Article Last Modified on 10/3/2003



APPLIES TO

  • Microsoft SQL Server 6.5 Standard Edition



This article was previously published under Q170446

BUG #: 16830

SYMPTOMS

You may experience the following error message when a database owner (DBO) assigns permissions for a view that contains 2^N - 1 columns, where N is >= 3 (such as 7, 15, 31, 63... in decimal or 111, 1111, 11111, 111111... in binary):

Msg 4601, Level 16, State 1
Only owner <%.*s> of object <%.*s>,in database %.*s
can GRANT/REVOKE this permission.


The following scripts demonstrate this problem:

Step 1: Connect to SQL Server as a systems administrator (SA) from ISQL/w.

   USE pubs
   GO
   sp_dropalias pubsdbo
   GO
   sp_droplogin pubsdbo
   GO
   sp_addlogin pubsdbo, NULL, pubs
   GO
   sp_addalias pubsdbo, dbo
   GO
   SETUSER
   GO
   IF EXISTS
      (SELECT NULL FROM sysobjects
       WHERE id = object_id('user1.user1view') and type = 'V')
      DROP VIEW user1.user1view
   GO
   sp_dropuser user1
   GO
   sp_dropuser user2
   GO
   sp_droplogin user1
   GO
   sp_droplogin user2
   GO
   sp_addlogin user1, NULL, pubs
   GO
   sp_addlogin user2, NULL, pubs
   GO
   sp_adduser user1
   GO
   sp_adduser user2
   GO
   GRANT CREATE VIEW TO user1
   GO
   SETUSER 'user1'
   GO
   IF EXISTS
      (SELECT NULL FROM sysobjects
      WHERE id = object_id('user1.user1view') and type = 'V')
      DROP VIEW user1.user1view
   GO
   CREATE VIEW user1view
   AS SELECT
         1 as c1,
         2 as c2,
         3 as c3,
         4 as c4,
         5 as c5,
         6 as c6,
         7 as c7
   GO
   GRANT ALL ON user1view TO DBO WITH GRANT OPTION
   GO
   SETUSER
   GO
                


Step 2: Connect to SQL Server and log on as "pubsdbo" with no password.

   GRANT SELECT ON user1.user1view TO user2
   GO
                

WORKAROUND

To work around this problem, create a view with the number of columns that has fewer or more than 2^N - 1 columns, where N >= 3.

STATUS

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

Keywords: kbbug kbprogramming kbusage KB170446