database permissions errors - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

database permissions errors

Author

Topic

Cruiser859
Starting Member

45 Posts

Posted - 2007-04-24 : 09:36:26

Hi

I recently moved a database from one server to another. I used sp_change_users_login to fix the orpan problem and most of the CMS with now work.

When I go to edit or create a new page I get the error below.

Could not find stored procedure 'XXXXX2006_GetParentPages'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Could not find stored procedure 'XXXXX2006_GetParentPages'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException: Could not find stored procedure 'XXXXX2006_GetParentPages'.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +742
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +45
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +5
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand command, CommandBehavior behavior) +28
System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) +88
DozingDogs.Classes.Users.GetParentPages(Int32 xf4e00b43c43dc707, Int32 x9978c1d85ad26976) +133
DozingDogs.Pages.EditPage.xc64206630ea5f259(Int32 x9978c1d85ad26976, Int32 x103b0c99eef08d17, DropDownList x38db8d199f9fa6da) +50
DozingDogs.Pages.EditPage.xde97676d01739ced(Object xe0292b9ed559da7d, EventArgs xfbf34718e704c6bc) +2292
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +750

The stored procedure it is looking for is there and it has the correct owner. Any ideas what is happening or how to fix this?

Thanks

Stewart

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-04-24 : 09:57:52

Does web app look at correct db?

Cruiser859
Starting Member

45 Posts

Posted - 2007-04-24 : 10:25:45

Yes, Everything works except when I try to edit a page or create a new one I receive the error above.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-04-24 : 10:34:45

How about permission on that sp?

Cruiser859
Starting Member

45 Posts

Posted - 2007-04-24 : 10:50:12

Yes the owner is the same and type is user. Is that what you are asking?

Thanks

Cruiser859
Starting Member

45 Posts

Posted - 2007-04-24 : 10:55:08

I sp_change_users_login to a specific owner name. Would it help to change the owner to dbo?

Cruiser859
Starting Member

45 Posts

Posted - 2007-04-24 : 11:00:09

I just tried to change it and received this error message.

Server: Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 96
Terminating this procedure. The Login name 'dbo' is absent or invalid.

Does this help any?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-04-24 : 12:51:02

Non-dbo owner is ok, but the app needs to call it with proper owner. The error you got means that dbo is not valid sql login account. If you like to map dbo to specific sql login, need to run sp_changedbowner instead. For permission part, ensure db user has exec permission on that sp.

Cruiser859
Starting Member

45 Posts

Posted - 2007-04-24 : 15:09:45

Thanks.

I will ignore the last error message. But the first error message is what is causing the problem.

I was just hoping changing to dbo would fix the problem.

Any suggestion on what I can do to fix the first error message?

Thanks

Stewart

Cruiser859
Starting Member

45 Posts

Posted - 2007-04-24 : 15:09:46

Thanks

Stewart

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-04-24 : 20:49:25

You mean 'can't find sp' error? If sp owner is not dbo and your app call it without specifying owner, you'll get that error.

Kristen
Test

22859 Posts

Posted - 2007-04-25 : 05:15:23

Is this the same problem as in your other thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82603

that the Sproc is actually missing from the database?

Is the Sproc present in the database, but with a different "owner" ?

Kristen

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2007-04-25 : 09:24:56

Try this. Log into the SQL Server with the same account that the web server is using. Check which database you begin in, especially if your connection string does not have the database attribute. Try running the stored procedure with query analyzer, and see what happens. You can also run

sp_helprotect XXXXX2006_GetParentPages

to check permissions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources