| Author |
Topic |
|
jbkayne
Posting Yak Master
100 Posts |
 Posted - 2003-01-27 : 13:30:24
|
| I recently installed SP3, and noticed that queries that spanned multiple databases (within the same server) stopped working. (i.e. select * from [db1].dbo.table1 inner join [db2].dbo.table1 ...)I have had to go back and add SELECT, INSERT, UPDATE, DELETE privilidges to the tables rather than using just EXECUTE priviliges on the stored procedure which has worked fine up until now. Anyone one else notice this? I didn't see any mention to this in the release notes. I would like to get back to just using EXECUTE grants if at all possible.Thanks for any insight to this issue. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-27 : 13:34:44
|
| Have you tried dropping and recreating all of the sprocs first? Give that a try if you haven't already. And if you can, try bouncing the server in between dropping and recreating. And also try revoking and re-granting EXEC permissions too. I have a feeling it's some weird quirk with the system tables that rebuilding everything might just fix. |
 |
|
|
jbkayne
Posting Yak Master
100 Posts |
Posted - 2003-01-27 : 14:15:18
|
| Rob,Thanks for the quick reply. I tried what you suggested and received the same scenario.I was successful in recreating the scenario from scratch. Here is the code I used. (You just need to add DB1 and DB2 prior to running).-- RUN AS SAif not exists (select * from master.dbo.syslogins where loginname = N'test') begin exec sp_addlogin 'test', 'test' endGOgouse db1goif exists (select * from master.dbo.syslogins where loginname = N'test') begin exec sp_revokedbaccess 'test' endexec sp_grantdbaccess 'test'GOcreate table test( testcol varchar(50) null)gouse db2goif exists (select * from master.dbo.syslogins where loginname = N'test') begin exec sp_revokedbaccess 'test' endexec sp_grantdbaccess 'test'GOcreate proc testasselect * from [DB1].dbo.testgoGRANT EXECUTE ON [dbo].[test] TO [test]GO-- RUN AS testexec test-- I get the following:-- Server: Msg 229, Level 14, State 5, Procedure test, Line 7-- SELECT permission denied on object 'test', database 'DB1', owner 'dbo'.Do you receive the message? or does it work for you? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-27 : 14:39:44
|
I haven't installed SP3 yet...and I don't think I'm gonna for the time being.  The code you sent worked fine for me, with no errors, so it's definitely an SP3 issue.Might want to consider giving Microsoft a call, if you haven't already. If it's a known bug the call won't cost you anything, and if they don't know about it you'd be doing them and everyone else a favor by reporting it. |
|
|
|
jbkayne
Posting Yak Master
100 Posts |
Posted - 2003-01-27 : 14:46:36
|
| OK, sounds like a bug then. Can you just confirm that you ran the procedure under the "test" user context. (not as sa)Well after reading about the Slammer Virus, my boss put some serious pressure on me to get all machines to SP3. Either way I was screwed. :)BTW, what is the best way to contact microsoft for this type of issue?Edited by - jbkayne on 01/27/2003 14:55:06 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-27 : 15:11:25
|
| I ran the code posted as a sysadmin and it created the login, table, and stored procedure without errors. It also executed the sproc correctly.I then tried running the procedure under the "test" login and it executed correctly. The "test" login does NOT have SELECT permissions on the table, only EXECUTE permissions on the sproc.SQL Server 2000/Version 8.00.534/SP 2/Windows 2000 Professional |
|
|
|
jbkayne
Posting Yak Master
100 Posts |
Posted - 2003-01-27 : 15:24:46
|
| Thanks Rob,You have been a tremendous help. |
|
|
|
verronep
Starting Member
15 Posts |
Posted - 2003-01-27 : 16:02:00
|
| I posted something here, but I think it more prudent to wait until I get home to test before I start something...Paul"I have not failed. I have just found 10,000 ways that won't work." - Thomas Alva Edison (1847-1931)Edited by - verronep on 01/27/2003 16:11:28 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-01-27 : 16:52:31
|
| Has anyone else discovered this problem with service pack 3? We only use stored procedures in our applications to get to the data, so this would be a major problem for us. We will not allow direct access to the tables. I guess we'll have to try it out on our own. |
|
|
|
Argyle
Yak Posting Veteran
53 Posts |
Posted - 2003-01-27 : 18:51:44
|
| Might have something to do with the new option "Allow Cross-Database Ownership Chaining" that you can check during SP3 install.Info:http://support.microsoft.com/default.aspx?scid=/support/servicepacks/SQL/2000/SP3ReadMe.asp#_enabling_cross45database_ownership_chain_ar5qFound some more info at:http://support.microsoft.com/?kbid=810474"In Microsoft SQL Server Service Pack 3 (SP3) Setup, a new dialog box has been added to allow the system administrator to control whether or not cross database ownership chaining will be permitted. If you select Enable cross-database ownership chaining for all databases during the SQL Server 2000 SP3 setup, you are enabling this option across all databases. This was the default behavior before SQL Server 2000 SP3.""If you have more than one database used by an application, and that application calls stored procedures or views in a database that is based on objects in another database, then cross-database ownership chaining is used. Applications that rely on cross-database ownership chaining may generate permission denied errors if cross-database ownership chaining option is turned off."/ArgyleEdited by - argyle on 01/27/2003 19:20:52 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-01-27 : 19:28:22
|
| We have at least one application that accesses more than one database and it does call an object on one database that calls an object on another database. So if this really is the problem, then I guess I'll just not select the option to enable it. I'm going to test this out tomorrow probably. |
|
|
|
|
SP3 Change???