BUG: Ownership Problem Across Databases When SA Is the DBO

ID: Q95885

The information in this article applies to:

Microsoft SQL Server for OS/2, version 4.2
Microsoft SQL Server version 4.2x


BUG# OS/2: 1609 (4.2)
       NT:  125 (4.2)

SYMPTOMS

Assume database1 and database2 both have SA as DBO, and both have User1 as database users. In database2, the DBO creates a stored procedure, SP1, which depends on a table, TABLE1, in database1 and grants the execution permission of stored procedure SP1 to User1. User1 can run the stored procedure SP1 until TABLE1 in database1 is dropped and recreated. After TABLE1 is dropped and recreated, when executing SP1, User1 will receive the following error message:

Permission denied on object TABLE1, database database1, owner
dbo (Msg 229, Level 14, State 1).

If the DBOs of the two databases are set to someone other than SA, User1 will be denied permission to access the underlying object the first time, or any time they try to run the stored procedure; which is exactly as documented.

CAUSE

Page 130 of the "System Administrator's Guide" states: "If the same user owns a stored procedure and all the views or tables it references, and if the procedure and the objects it references are all in the same database, SQL Server checks only the permissions on the procedure."

Since there is only one SA server-wide, it is not unusual that SQL server does not check the permission on the underlying table which is in another database when SA are the DBOs in both databases. However, the problem occurs when SQL Server begins to check the permission on the underlying table when the table is dropped and recreated although SA is still the DBO in both databases and the table is still owned by the DBO.

WORKAROUND

Recreate the stored procedure SP1 and re-grant the permission to the user in database2 when the table in database1 is dropped and recreated.

STATUS

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

Additional query words: permission Windows NT

Keywords : kbother kbbug4.20 kbbug4.20a SSrvServer SSrvWinNT
Version : 4.2 | 4.2
Platform : OS/2 WINDOWS
Issue type :