I’m not sure if this is a .NET question or a database question:confused:
I have migrated a database from one server to another and following the migration had an orphaned user (WholeSystem_webuser), which has been fixed using the auto_fix option of sp_change_users_login
• The WholeSystem_webuser account is used by ASP.NET pages to connect to the database and run stored procedures.
• I can log in to Management Studio as WholeSystem_webuser OK and execute the sp’s from there
• When I try to run my ASP.NET page it was saying “Can’t find stored procedure [procedure name]”
• If I drop the sp and create it as [dbo].[GetContactsCount] instead of [WholeSystem_ webuser]. [GetContactsCount] it says “The EXECUTE permission was denied on the object ‘GetContactsCount’, database ‘WholeSystem’, schema ‘dbo’.” (which is what I’d expect as the connection string is using the WholeSystem_webuser account??) –does that show that the web user is logging into the database OK??
• If I try and configure the SqlDataSource in VWD to see what it is looking at though, it won’t let me get beyond the connection string window and says “Default schema could not be retrieved for this connection”
• I tried setting up a new login/user/connection string but still get the
“Default schema could not be retrieved for this connection” error
IN SQL Server:
• Users mapped to the login WholeSystem_webuser - WholeSystem_webuser, default schema dbo
• User WholeSystem_webuser has default schema dbo and db_dataread and db_datawrite permissions
• There is a schema dbo whose owner is dbo, and a schema WholeSystem_webuser whose owner is WholeSystem_webuser
Any clues as to where to start with this please?
Thanks
Jackie