September 30, 2003 at 2:06 pm
In our development environment, we had several databases in which the db_owner was a user rather then the default dbo. Although some objects were owned by the default dbo, most were owned by the user db_owner, USER1. Users could access objects owned by either the dbo or USER1 db_owner without specifying the owner.
Just recently we imported a database from another site by restoring a backup. The new database contained a db_owner user with the same user name, USER1, that already existed in the other development databases on the server. Immediately, users logging in from a web application with the USER1 db_owner user name could no longer access functions and stored procedures with out using the owner name as a prefix. For example, the call to function sf_GetAllPersons() now required specification of the owner, USER1.sf_GetAllPersons(). Any objects owned by the default dbo did not require a db_owner prefix. Once we diagnosed the problem, we ran sp_change_user_login to update the spid of the db_owner USER1 in the new database to the same value as the USER1 in the master database sysxlogin table. The update was successful, but the problem continued to persist. SQL Server continued to require the use of the user name as a prefix to calling the stored procedures and functions owned by that user. This was true not only in the new database, but in all the old databases as well.
We deleted the new database, shut down the server, and restarted it, but the problem persisted.
db_owner USER1 has public, db_owner and db_ddladmin roles in all the databases.
The people who sent us the database said that they had a similar problem not to long ago, but fixed it by running sp_change_user_login. Once they changed the spid, SQL Server no longer required the owner prefix when calling the functions and stored procedures.
We tentatively fixed the problem by renaming the owner of all the objects to the default, 'dbo'.
Can anyone explain why this problem persists and what we can do to fix it and/or avoid it in the future? Your comments are appreciated.
Thomas Farren
October 3, 2003 at 5:03 am
Without being able to look directly at the database I cannot speculate exactly why. May be some piece did not get fixed and was throwing it out incorrectly. However this type of problem is the main reason why all objects should be owned by DBO and not a user as there are variables that can cause issues. To help in the future use the following
sp_MSForEachTable 'sp_changeobjectowner ''?'',''OwnerNameHere'''
this will change all objects to being owned by dbo. However if two objects have the same name owned by dbo and a user then this could be a reason for your issue. The above code will error should it try to set two to dbo.
October 3, 2003 at 11:29 am
Antares:
Thanks for taking the time to respond to this posting. As you advised, we decided to give dbo ownership of all objects in our development environment.
To solve our problem, we wrote our own script to change the owner by scanning the sysobjects file. We did this because I was unaware of the system storedprocedures that perform similar tasks. The sp_MSForEachTable procedure didn't quite meet our requirements, because it only works with table objects, and our problem included stored procedures and inline functions. I copied sp_MSForEachTable to a new procedure and tweaked it a little to handle the other object types, and it worked like a charm. Our own script, however, did perform the task a little quicker.
Thomas Farren
Thomas Farren
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply