Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've run to a problem in which I'm not sure where to look or how to fix it. The problem is the following. We have a site with a sql2000database. A backup from this was restored to create a development enviroment. Some of the store procedures are named [owner].name (owner is not dbo and I would want to keep the same structure in both live and development enviroment)

When I try to run those store procedures without specifing the owners name I get a "could not find store procedure" error. (using the owners credentials)

But I create a new user, and a new store procedure where this new users is the owner. And then login with the new user. Then I can find the store procedure by its name.

Example:

Owner = Daniel SPTest = store procedure (name Daniel.SPTest)

Login as Daniel, exec SPTest = store procedure not found <-- what is wrong here?

Create a new user and store procedure in the database Owner New User SPTest2 = Store procedure (name New User.SPTest2) Login as New User exec SPTest2 = runs procedure

I've tried to delete the user Daniel, then creating a new user with the username Daniel. But I still recieved the same error. Is there any other table where this kind of mapping is located?

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

Create your user 'Daniel' on the server (not just for that database, but in security objects for the server) then run:

USE YOUR_SQL_2000DB
sp_change_users_login 'auto_fix', 'Daniel'

The problem (most likely) is that your database is not viewing your new 'Daniel' as the same user as the old 'Daniel'. That system sproc should repair that.

share|improve this answer
    
That would probably fix the problem. I did something similar to fix the issue though. I created a new user, changed the ownership to the new user. Then deleted the old one, created a new user with the old name and then changed back the ownership and then it worked. But your solution is probably the right way to go :) –  Daniel Sep 14 '10 at 11:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.