Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2002-10-10 : 19:53:18
|
| I've got 10 databases, and one username that is 'dbo' for all 10.My SQL CDONTS function (UDF) can be dropped and created properly in all 10 databases. It shows the proper owner (dbo) in all 10 databases. The Username shows as dbo in all 10 databases.BUTOnly 3 of the databases allow select dbo.EmailCDONTS(parameter list)The other 7 return the errorServer: Msg 229, Level 14, State 5, Procedure EmailCDONTS, Line 34EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.I'd like to understand why database 'master' is involved in this error message.This must be a setup problem involving the database and user permissions?Any help appreciated.Sam |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-10-11 : 04:36:16
|
| If a login is given permission on a stored procedure in a database that stored procedure is able to access anything within that database. If the stored procedure tries to access something in another database the permissions for the login are again checked.The login that is connecting to your working datbases presumably also has permission on sp_OACreate on master - the others don't I would guess.Master is involved because EmailCDONTS calls sp_OACreate which resides in master.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-10-11 : 07:39:54
|
| Thanks Nigel. Here's the twist.I login to QA one time, using my 'super' User ID that has 'dbo' permissions in all my databases.Without logging out again, I can drop, create, and grant permissions of this function in all databases.When I execute dbo.EmailCDONTS it works properly in 3 databases and returns:Server: Msg 229, Level 14, State 5, Procedure EmailCDONTS, Line 34 EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'. Strange to get an error *for the same dbo user* in 7 other databases.It seems to me that my 'super' user should have the same permissions in master no mater what database it was using. Somehow, this permission is changing depending on the database in use, or could it be some other problem?SamEdited by - SamC on 10/11/2002 09:02:14 |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-10-11 : 09:11:38
|
| The sp_OA SPs are extended stored procs so maybe it's something to do with the dll?Try just running sp_OACreate from the various databases.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-10-11 : 09:28:30
|
| It's sort of interesting that NONE of the databases hosted at my hosting provider will allow me to execute the following 3 statements in QA:Declare @MailID intDeclare @hr intEXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUTBut if I wrap it in a UDF with Public execute permission, it will work in the 3 databases mentioned.Now I have two new questions: 1- Why won't QA execute the 3 lines above? What permissions do I look for? 2- Why is the UDF working in 3 databases when the sp_OACreate will not execute stand alone in QA?SamEdited by - SamC on 10/11/2002 09:39:59Edited by - SamC on 10/11/2002 09:54:10 |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-10-11 : 10:10:46
|
| Maybe I'm going about this all wrong.So that a 'Public' user can execute sp_OACreate...What permissions should the ROLE Public, users dbo and guest be set to in the database master ??Sam |
|
|
|
|
|
|
|
|
Permissions Problem?