hfjeremy
asked on
SQL Server: Could not find stored procedure 'User_Proc' on migrated database
Hey, everyone -
Here's the problem. I took a perfectly working VB.NET app, zipped up all the code, detached the databases, and copied it all to a different server (Windows 2000 Server). I have the site set up in IIS and working fine, it would seem, except for the database.
The database was installed on SQL Server 2000, and that's what I'm running the new copy on, as well. I used the "Attach Database" function in Enterprise Manager, and everything seemed to go swimmingly. Problem is, I really don't know too much about the ASP.NET and SQL Server user schemes, so I've had a couple errors here and there. First I couldn't connect to the database at all, until I reattached using owner "TestUser". TestUser's also the user who owns the stored procedure I'm trying to access (they were all moved over with the rest of the database). Now I can connect to the database, but I get the error specified in the subject: "Could not find stored procedure 'User_Proc'."
The ASP.NET authentication mode (set in Web.config) is Windows, if that makes any difference, and I've created a Windows user called 'TestUser'. I'm not sure what groups he has to be a member of, if any in particular. Right now he's in Users and Administrators.
Now, the code is exactly as it was on the other server, and it's running. The database contents (and I suppose all the settings that get moved with the database mdf and ldf files) are also as they were. I've read elsewhere online that the problem could be that the SP name isn't fully qualified (prefixed with database and/or owner name); I've done a direct query, and I'm able to execute the SP if I prefix it with the owner name...so that's probably the deal. BUT...the code is pretty extensive, and makes calls to dozens of SPs all over the place, and I don't want to edit them all to do these prefixes, recompile, etc.
So is there a way to change the setup of the server and/or SQL Server to allow the code to work as before? Do I have to run IIS as TestUser or something?
Thanks,
Jeremy
Here's the problem. I took a perfectly working VB.NET app, zipped up all the code, detached the databases, and copied it all to a different server (Windows 2000 Server). I have the site set up in IIS and working fine, it would seem, except for the database.
The database was installed on SQL Server 2000, and that's what I'm running the new copy on, as well. I used the "Attach Database" function in Enterprise Manager, and everything seemed to go swimmingly. Problem is, I really don't know too much about the ASP.NET and SQL Server user schemes, so I've had a couple errors here and there. First I couldn't connect to the database at all, until I reattached using owner "TestUser". TestUser's also the user who owns the stored procedure I'm trying to access (they were all moved over with the rest of the database). Now I can connect to the database, but I get the error specified in the subject: "Could not find stored procedure 'User_Proc'."
The ASP.NET authentication mode (set in Web.config) is Windows, if that makes any difference, and I've created a Windows user called 'TestUser'. I'm not sure what groups he has to be a member of, if any in particular. Right now he's in Users and Administrators.
Now, the code is exactly as it was on the other server, and it's running. The database contents (and I suppose all the settings that get moved with the database mdf and ldf files) are also as they were. I've read elsewhere online that the problem could be that the SP name isn't fully qualified (prefixed with database and/or owner name); I've done a direct query, and I'm able to execute the SP if I prefix it with the owner name...so that's probably the deal. BUT...the code is pretty extensive, and makes calls to dozens of SPs all over the place, and I don't want to edit them all to do these prefixes, recompile, etc.
So is there a way to change the setup of the server and/or SQL Server to allow the code to work as before? Do I have to run IIS as TestUser or something?
Thanks,
Jeremy
Who is the dbowner now?
EXEC sp_changedbowner 'Albert' -- you can use this to change the dbowner to what it was before.
Now, you can get this error message of execute permissions are nore granted on stored procedures. So make sure execute permissions are granted to the correct users on stored procs.
regards-
EXEC sp_changedbowner 'Albert' -- you can use this to change the dbowner to what it was before.
Now, you can get this error message of execute permissions are nore granted on stored procedures. So make sure execute permissions are granted to the correct users on stored procs.
regards-
ASKER
Esteban,
Server=dysprosium;Database
Replace my previous usage of 'TestUser' with 'ProxyUser' -- it's actually ProxyUser throughout the system.
-Jeremy
Server=dysprosium;Database
Replace my previous usage of 'TestUser' with 'ProxyUser' -- it's actually ProxyUser throughout the system.
-Jeremy
Connect as proxyuser within query analyser and try to execute User_Proc
exec User_Proc
if you get an permission related error message then you need to grant permissions.
grant execute on User_Proc to proxyuser
regards-
exec User_Proc
if you get an permission related error message then you need to grant permissions.
grant execute on User_Proc to proxyuser
regards-
hfjeremy,
I'll assume that ProxyUser is a SQL user (not a windows user). If so, change the owner of the database to ProxyUser or refer the stored procedure using the qualified name.
I'll assume that ProxyUser is a SQL user (not a windows user). If so, change the owner of the database to ProxyUser or refer the stored procedure using the qualified name.
ASKER
Namasi,
When I attach the database, I have two choices of owner -- 'sa' and 'ProxyUser'. If I choose sa, I get the error:
"Cannot open database requested in login 'SuperDatabase'. Login fails. Login failed for user 'ProxyUser'"
(Some of that may be application error messages, but the final part's direct from the DB, I think.)
Then when I either attach with owner ProxyUser, or use sp_changedbowner 'ProxyUser' after the fact, it can connect (I'm presuming), but can't find the stored proc.
-Jeremy
When I attach the database, I have two choices of owner -- 'sa' and 'ProxyUser'. If I choose sa, I get the error:
"Cannot open database requested in login 'SuperDatabase'. Login fails. Login failed for user 'ProxyUser'"
(Some of that may be application error messages, but the final part's direct from the DB, I think.)
Then when I either attach with owner ProxyUser, or use sp_changedbowner 'ProxyUser' after the fact, it can connect (I'm presuming), but can't find the stored proc.
-Jeremy
try
sp_helptext User_Proc
to see if you get any results back.
Also, you may need to connect as sa and add proxyuser if proxyuser does not exist.
sp_helptext User_Proc
to see if you get any results back.
Also, you may need to connect as sa and add proxyuser if proxyuser does not exist.
ASKER
Namasi:
I can execute it directly in QA, provided I fully qualify the SP name with the owner name (ProxyUser.User_Proc). ProxyUser is the owner of User_Proc, and has execute permissions on it.
Esteban:
ProxyUser is indeed a SQL Server user (though I also created a Windows account for him, since I didn't know what I was doing, and wanted to cover all the bases :). The owner of the database is ProxyUser, but my code still doesn't work without fully qualifying the SP name. And as I said in the original Q, I don't want to do that because it's simply too many (dozens, if not hundreds) of code references to change. There's got to be a way to get it to work without doing that, if it did on the original server.
-Jeremy
I can execute it directly in QA, provided I fully qualify the SP name with the owner name (ProxyUser.User_Proc). ProxyUser is the owner of User_Proc, and has execute permissions on it.
Esteban:
ProxyUser is indeed a SQL Server user (though I also created a Windows account for him, since I didn't know what I was doing, and wanted to cover all the bases :). The owner of the database is ProxyUser, but my code still doesn't work without fully qualifying the SP name. And as I said in the original Q, I don't want to do that because it's simply too many (dozens, if not hundreds) of code references to change. There's got to be a way to get it to work without doing that, if it did on the original server.
-Jeremy
Are you using impersonation in your asp.net app? Can you launch the SqlProfiler and check what user is connecting to the database?
ASKER
Namasi:
ProxyUser definitely exists. I wasn't able to get sp_helptext to work, because QA can't find User_Proc without it being fully qualified (same as with executing User_Proc directly), and when I fully qualify it, I get errors with the periods in the name.
Esteban:
Nope, impersonation's not set in Web.config, and Profiler shows the connection as being from ProxyUser.
It seems like I just need to create the conditions under which I don't need to fully qualify the SP name, right? Should I not need to do that if the connection is coming from ProxyUser, when ProxyUser owns the database?
-Jeremy
ProxyUser definitely exists. I wasn't able to get sp_helptext to work, because QA can't find User_Proc without it being fully qualified (same as with executing User_Proc directly), and when I fully qualify it, I get errors with the periods in the name.
Esteban:
Nope, impersonation's not set in Web.config, and Profiler shows the connection as being from ProxyUser.
It seems like I just need to create the conditions under which I don't need to fully qualify the SP name, right? Should I not need to do that if the connection is coming from ProxyUser, when ProxyUser owns the database?
-Jeremy
If ProxyUsers owns the sp and it's connecting as ProxyUser there should not be any problem. Are you sure that you code is not calling dbo.yourSp somewhere?
Who calls the problematic sp? ¿asp.net? ¿another sp?
ASKER
Esteban:
asp.net calls it in the web app. But to simplify the issue, I can connect to the database in Query Analyzer as ProxyUser, and execute the following:
use SuperDatabase;
execute User_Proc;
To which I get this:
"Could not find stored procedure 'Get_Valid_Active_User_v2'
I remedy the situation by prefixing User_Proc:
use SuperDatabase;
execute ProxyUser.User_Proc;
In which case I get:
Procedure 'User_Proc' expects parameter '@Username', which was not supplied.
So, even though I didn't take the time to supply that parameter, it means it's finding the SP with the fully qualified name, and trying to execute it.
-Jeremy
asp.net calls it in the web app. But to simplify the issue, I can connect to the database in Query Analyzer as ProxyUser, and execute the following:
use SuperDatabase;
execute User_Proc;
To which I get this:
"Could not find stored procedure 'Get_Valid_Active_User_v2'
I remedy the situation by prefixing User_Proc:
use SuperDatabase;
execute ProxyUser.User_Proc;
In which case I get:
Procedure 'User_Proc' expects parameter '@Username', which was not supplied.
So, even though I didn't take the time to supply that parameter, it means it's finding the SP with the fully qualified name, and trying to execute it.
-Jeremy
is 'Get_Valid_Active_User_v2'
ASKER
Esteban,
Sorry about that, that's a different one I was trying it on. Result's the same. Treat that as User_Proc.
-Jeremy
Sorry about that, that's a different one I was trying it on. Result's the same. Treat that as User_Proc.
-Jeremy
Post the code of the problematic stored procedure... Or all sp fail?
ASKER
Esteban:
Nope, they all fail -- it's not even finding them to execute the code in the first place. Here's the question, boiled down:
If I go into Query Analyzer and connect to SuperDatabase as ProxyUser, what do I have to do so that I don't need to fully qualify the name of a stored procedure (owned by ProxyUser) in order to execute it? And if I do have to fully qualify the name, what's wrong? What Windows/SQL Server settings do I have to change?
It'll all be solved if I can connect as ProxyUser in Query Analyzer, type the following, and have it run without error:
use SuperDatabase;
execute User_Proc;
-Jeremy
Nope, they all fail -- it's not even finding them to execute the code in the first place. Here's the question, boiled down:
If I go into Query Analyzer and connect to SuperDatabase as ProxyUser, what do I have to do so that I don't need to fully qualify the name of a stored procedure (owned by ProxyUser) in order to execute it? And if I do have to fully qualify the name, what's wrong? What Windows/SQL Server settings do I have to change?
It'll all be solved if I can connect as ProxyUser in Query Analyzer, type the following, and have it run without error:
use SuperDatabase;
execute User_Proc;
-Jeremy
OMG, what a mess! :P...
Let's try this queries:
SELECT *
FROM sysusers
WHERE (name = N'ProxyUser')
SELECT *
FROM sysobjects
WHERE (xtype = 'P')
Let's check that object ownership and users are well configured. Post the results (or some of them)
Let's try this queries:
SELECT *
FROM sysusers
WHERE (name = N'ProxyUser')
SELECT *
FROM sysobjects
WHERE (xtype = 'P')
Let's check that object ownership and users are well configured. Post the results (or some of them)
ASKER
No kidding! This is some irritating stuff...hence the rise in points. :)
Okay, as for the first query, I get one result: UID=6; status=2; name=ProxyUser; roles=0x03; altuid=0; password=NULL; gid=0; environ=NULL; hasdbaccess=1; islogin=1; isntname=0; isntgroup=0; isntuser=0; issqluser=1; isaliased=0; issqlrole=0; isapprole=0
As for the second...lots of SPs are returned, including User_Proc. For User_Proc:
uid=6; info=0; status=0; base_schema_ver=0; replinfo=0; parent_obj=0; ftcatid=0; schema_ver=0; stats_schema_ver=0; type=P; userstat=0; sysstat=4; indexdel=0; version=0; deltrig=0; instrig=0; updtrig=0; selftrig=0; category=0; cache=0.
Anything useful there? At least the UID matches...I was wondering if the usernames were the same somehow, but the user was a different object. Apparently not.
-Jeremy
Okay, as for the first query, I get one result: UID=6; status=2; name=ProxyUser; roles=0x03; altuid=0; password=NULL; gid=0; environ=NULL; hasdbaccess=1; islogin=1; isntname=0; isntgroup=0; isntuser=0; issqluser=1; isaliased=0; issqlrole=0; isapprole=0
As for the second...lots of SPs are returned, including User_Proc. For User_Proc:
uid=6; info=0; status=0; base_schema_ver=0; replinfo=0; parent_obj=0; ftcatid=0; schema_ver=0; stats_schema_ver=0; type=P; userstat=0; sysstat=4; indexdel=0; version=0; deltrig=0; instrig=0; updtrig=0; selftrig=0; category=0; cache=0.
Anything useful there? At least the UID matches...I was wondering if the usernames were the same somehow, but the user was a different object. Apparently not.
-Jeremy
ASKER
And yes, I see online in several places (for instance, http://web.zdnet.com.au/builder/architect/database/story/0,2000034918,20277786,00.htm) that when ownername isn't specified as part of the SP name, it defaults to the current username, "as determined by the login of the current connection." So...what the heck is going on here? You're right -- if User_Proc is owned by ProxyUser, and I connect as ProxyUser, we should be good to go!
-Jeremy
-Jeremy
Reading the queries results I see that ProxyUser has a uid=6 and that's the uid of User_Proc.
Damn, I feel so lost now!
I'll need a couple of minutes to think....
Damn, I feel so lost now!
I'll need a couple of minutes to think....
ok.. question. You said that you created a windows user with the same name. Can you check in your database user you don't have a ProxyUser and a DOMAIN\ProxyUser ?
ASKER
Since I made that comment about creating the Windows user, I have deleted that Windows account. I wanted to see if the web stuff would still work without it, and it does; I didn't see any change. So no, there's no DOMAIN\ProxyUser in the Users table for the database.
The database has three users:
Name Login Name Database Access
--------------------------
dbo sa Permit
Joe Permit
ProxyUser Permit
As for the UID thing, I figured that ProxyUser's UID is 6, and that User_Proc's UID refers to its owner, which is UID 6, or ProxyUser. Which would mean that all is as it should be, as far as ownership goes.
-Jeremy
The database has three users:
Name Login Name Database Access
--------------------------
dbo sa Permit
Joe Permit
ProxyUser Permit
As for the UID thing, I figured that ProxyUser's UID is 6, and that User_Proc's UID refers to its owner, which is UID 6, or ProxyUser. Which would mean that all is as it should be, as far as ownership goes.
-Jeremy
Why ProxyUser doesn't have a login name? There should be
Name Login Name Database Access
--------------------------
dbo sa Permit
Joe Joe Permit
ProxyUser ProxyUser Permit
Name Login Name Database Access
--------------------------
dbo sa Permit
Joe Joe Permit
ProxyUser ProxyUser Permit
ASKER
Hmm...I'm not sure. It seems like that users table was pulled over verbatim with the rest of the database information in the .mdf file, though, so I would think it was working that way on the other machine just fine. That is, unless the login name is a dependency lying somewhere outside of the database itself, either in SQL Server or Windows. But I don't see the user "sa" listed in my Windows user accounts, so it doesn't seem like that's it...
If it needs a login name, any idea how/where that's done?
-Jeremy
If it needs a login name, any idea how/where that's done?
-Jeremy
I think that information is stored in the table sysxlogins in master database. But i don't like touching the master database. I'm not sure if i should suggest you to modify it.
Maybe a better idea would be to create a new user from scratch and change the ownership of all objects.
Please backup everything (including system databases) before touching master database, if you decide to do it.
Maybe a better idea would be to create a new user from scratch and change the ownership of all objects.
Please backup everything (including system databases) before touching master database, if you decide to do it.
ASKER
I took a look at master, and it wasn't too clear. I did a little research, though, and found the built-in SPs sp_addlogin, sp_helplogins, and others. sp_helplogins seems to be the most meaningful so far (refer here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_help_50mr.asp).
Command [executed as Administrator using Windows login]: EXEC sp_helplogins 'ProxyUser'
Output:
LoginName SID DefDBName DefLangName AUser ARemote
--------------------------
ProxyUser blahblahblah (long) master us_english yes no
==========================
LoginName DBName UserName UserOrAlias
--------------------------
ProxyUser SuperDatabase db_owner MemberOf
ProxyUser SuperDatabase dbo User
Think those duplicate entries in the second portion are trouble?
-Jeremy
Command [executed as Administrator using Windows login]: EXEC sp_helplogins 'ProxyUser'
Output:
LoginName SID DefDBName DefLangName AUser ARemote
--------------------------
ProxyUser blahblahblah (long) master us_english yes no
==========================
LoginName DBName UserName UserOrAlias
--------------------------
ProxyUser SuperDatabase db_owner MemberOf
ProxyUser SuperDatabase dbo User
Think those duplicate entries in the second portion are trouble?
-Jeremy
Try to set SuperDatabase as the default DB for ProxyUser
You can use the following cmd to do it
sp_defaultdb 'ProxyUser', 'SuperDatabase'
thus you won't have to prefix any more
HTH
Hilaire
You can use the following cmd to do it
sp_defaultdb 'ProxyUser', 'SuperDatabase'
thus you won't have to prefix any more
HTH
Hilaire
ASKER
Man, it sounded so promising...:(
Your query executed without errors, but I still can't connect as ProxyUser through Query Analyzer and execute User_Proc without prefixing it with the owner name (ProxyUser). This is so bizarre, and no one seems to know how to solve it. Raised to 200.
-Jeremy
Your query executed without errors, but I still can't connect as ProxyUser through Query Analyzer and execute User_Proc without prefixing it with the owner name (ProxyUser). This is so bizarre, and no one seems to know how to solve it. Raised to 200.
-Jeremy
Can you check that the default DB has been successfully changed ?
For this, execute again
EXEC sp_helplogins 'ProxyUser'
You should get SuperDatabase as DefDBName
I think sp_defaultdb 'ProxyUser', 'SuperDatabase' must be executed using the 'sa' account,
and it be take effect only at the next connexion on ProxyUser
HTH
Hilaire
For this, execute again
EXEC sp_helplogins 'ProxyUser'
You should get SuperDatabase as DefDBName
I think sp_defaultdb 'ProxyUser', 'SuperDatabase' must be executed using the 'sa' account,
and it be take effect only at the next connexion on ProxyUser
HTH
Hilaire
ASKER
Yep, it's the default DB, all right...that query went off fine. I did execute it as sa. I've restarted SQL Server and IIS both, as well as the SQL visual tools, and everything remains the same. *sigh*
-Jeremy
-Jeremy
I guess the problem lies in differences between Login and User
Login 'ProxyUser' has the dbo UserProfile
The Db is owned by the 'ProxyUser' User, not by dbo
try
connect as sa
use SuperDatabase
exec sp_changedbowner 'ProxyUser'
or, alternatively
make the 'ProxyUser' login a member of the 'ProxyUser' user profile
EXEC sp_change_users_login 'Update_One', 'ProxyUser', 'ProxyUser'
HTH
Hilaire
Login 'ProxyUser' has the dbo UserProfile
The Db is owned by the 'ProxyUser' User, not by dbo
try
connect as sa
use SuperDatabase
exec sp_changedbowner 'ProxyUser'
or, alternatively
make the 'ProxyUser' login a member of the 'ProxyUser' user profile
EXEC sp_change_users_login 'Update_One', 'ProxyUser', 'ProxyUser'
HTH
Hilaire
Issue a
use SuperDatabase
for the second syntax too
Hilaire
use SuperDatabase
for the second syntax too
Hilaire
ASKER
Hmm, interesting...neither one of those queries worked.
use SuperDatabase
exec sp_changedbowner 'ProxyUser'
...produces the error: "The proposed new database owner is already a user in the database."
use SuperDatabase
EXEC sp_change_users_login 'Update_One', 'ProxyUser', 'ProxyUser'
...produces the error: "The login already has an account under a different user name."
-Jeremy
use SuperDatabase
exec sp_changedbowner 'ProxyUser'
...produces the error: "The proposed new database owner is already a user in the database."
use SuperDatabase
EXEC sp_change_users_login 'Update_One', 'ProxyUser', 'ProxyUser'
...produces the error: "The login already has an account under a different user name."
-Jeremy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
So.. it's your site up now? :)
ASKER
Well, I'm going to assume you're right -- I may very soon try those ideas, particularly the first (because I want to see if it's possible in ANY way with the existing database), but for now I just went through and edited all the code to fully qualify the SP references. The app's working now because of that, but I really want to avoid this in the future, so I'll try to continue figuring it out.
Thanks to everyone who attempted to help; if the database is, in fact, corrupted, a correct answer would be extremely hard, if not impossible to find. I lost so much sleep last night that I'm not really ready to do it again. :) Particular thanks to Esteban, who was there consistently for many hours.
-Jeremy
Thanks to everyone who attempted to help; if the database is, in fact, corrupted, a correct answer would be extremely hard, if not impossible to find. I lost so much sleep last night that I'm not really ready to do it again. :) Particular thanks to Esteban, who was there consistently for many hours.
-Jeremy
Well.. let us know if you find out something interesting.
My last advice. Next time you need to deploy a database, use the second method i recommended. I deploy different websites almos every week, and that method is in my experience the safer.
Cheers,...
Esteban Felipe
www.estebanf.com
My last advice. Next time you need to deploy a database, use the second method i recommended. I deploy different websites almos every week, and that method is in my experience the safer.
Cheers,...
Esteban Felipe
www.estebanf.com
ASKER
Yeah, this is the first time I've ever dealt with SQL Server at all, actually. I was provided the mdf and ldf files, and a quick search online showed that I could simply attach them to my server (provided it's the same version), so that's what I did. I had no idea the permissions and ownership stuff would be such a labyrinthine mess, or that so much could go wrong. I still can't believe it's that complex -- I simply detached a DB with all its settings and contents and reattached it somewhere else, for cryin' out loud.
Anyway, man, thanks for the help and advice.
-Jeremy
Anyway, man, thanks for the help and advice.
-Jeremy
I am having this exact problem. Nearly everytime I detach a database and re-attach it to the SAME SQL Server instance I receive permission errors. The database user I want to connect as still exists in the actual instance (i.e. under Security/Logins in Enterprise Manager), the user also exists in the database (i.e. Databases/DB_NAME/user), the user also has all previous permissions on all objects. Go to the web app and I get....
<i>Microsoft OLE DB Provider for SQL Server (0x80004005) Cannot open user default database. Login failed.</i>
Ok...well the user does not have permissions granted on the database (Security/Logins/Username Properties check the database access tab). Easy right? I select the box for my database to assign this user rights to the database....but I get the error....
<i>Error: 21002: [SQL-DMO]User 'user_name' already exists.</i>
Well this makes sense because I can see the user on the database. But the instance thinks that this login does not have rights to the database.
Confused? Me too....
So how do I have permissions on the database but the instance thinks I am not authorized to log into it. I have dealt with this before (and again today) with no luck - I thought I could solve it today. My previous solution is to delete the user from the newly re-attached database, then add them in the Security/Logins then add their permissions back to all of the stored procedures they should access. This is a HORRIBLE method and if this is how MS inteded detach/attach to work then shame on them. I cannot believe that there is no easier way to fix this.
Help!!
<i>Microsoft OLE DB Provider for SQL Server (0x80004005) Cannot open user default database. Login failed.</i>
Ok...well the user does not have permissions granted on the database (Security/Logins/Username Properties check the database access tab). Easy right? I select the box for my database to assign this user rights to the database....but I get the error....
<i>Error: 21002: [SQL-DMO]User 'user_name' already exists.</i>
Well this makes sense because I can see the user on the database. But the instance thinks that this login does not have rights to the database.
Confused? Me too....
So how do I have permissions on the database but the instance thinks I am not authorized to log into it. I have dealt with this before (and again today) with no luck - I thought I could solve it today. My previous solution is to delete the user from the newly re-attached database, then add them in the Security/Logins then add their permissions back to all of the stored procedures they should access. This is a HORRIBLE method and if this is how MS inteded detach/attach to work then shame on them. I cannot believe that there is no easier way to fix this.
Help!!
Alright - solution found. If this helps your problem as well I found this from user arbert in question Q_20839560.
After you re-attach the db..if you have the problem I had (which yours sounds like) then you can use the QA and run sp_change_users_login. Check BO for the usage, but if you run it in the DB that was attached the syntax to fix the problem is
exec sp_change_users_login 'Update_One', 'your_user_name1', 'your_user_name2'
What you are really doing is mapping the database login for your_user_name1 to the sql server login for your_user_name2. In my case, both names are identical (because the names already existed). This worked perfectly and I am back up in running. Heck of a lot easier than editing a bunch of code ;)
After you re-attach the db..if you have the problem I had (which yours sounds like) then you can use the QA and run sp_change_users_login. Check BO for the usage, but if you run it in the DB that was attached the syntax to fix the problem is
exec sp_change_users_login 'Update_One', 'your_user_name1', 'your_user_name2'
What you are really doing is mapping the database login for your_user_name1 to the sql server login for your_user_name2. In my case, both names are identical (because the names already existed). This worked perfectly and I am back up in running. Heck of a lot easier than editing a bunch of code ;)
Oh yeah....I always re-attached the database as the the dbo we use - this is not the same as your_user_name in my case. The users' login is NEVER the database owner. So you don't have to attach it as that name if you don't want to - I noticed you changed the owner to 'make it work'.
What's your connection string?...
Cheers!