We help IT Professionals succeed at work.

SQL Server: Could not find stored procedure 'User_Proc' on migrated database

hfjeremy
hfjeremy asked
on
3,772 Views
Last Modified: 2007-12-19
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

Comment
Watch Question

Hi hfjeremy,
What's your connection string?...

Cheers!
CERTIFIED EXPERT

Commented:
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-

Author

Commented:
Esteban,

Server=dysprosium;Database=SuperDatabase;UID=ProxyUser;PWD=;Connect Timeout=1

Replace my previous usage of 'TestUser' with 'ProxyUser' -- it's actually ProxyUser throughout the system.

-Jeremy
CERTIFIED EXPERT

Commented:
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-
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.

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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
Are you using impersonation in your asp.net app? Can you launch the SqlProfiler and check what user is connecting to the database?

Author

Commented:
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
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?

Author

Commented:
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

is 'Get_Valid_Active_User_v2' a stored procedure you made? who's the owner of that stored procedure?

Author

Commented:
Esteban,

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?

Author

Commented:
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
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)

Author

Commented:
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

Author

Commented:
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
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....

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 ?

Author

Commented:
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
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

Author

Commented:
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
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.

Author

Commented:
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


Commented:
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

Author

Commented:
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

Commented:
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

Author

Commented:
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

Commented:
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

Commented:
Issue a
use SuperDatabase
for the second syntax too

Hilaire

Author

Commented:
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
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
So.. it's your site up now? :)

Author

Commented:
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
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

Author

Commented:
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

Commented:
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!!

Commented:
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 ;)

Commented:
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'.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.