January 22, 2013 at 1:30 pm
hi folks,
I work in a customer support dpt for an application that makes heavily use of stored procedures for loading data.
Recently one of my customers migrated from SQL server 2000 to 2008 R2 and then there had been some major problems.
1st of all there had been a user called c with a default schema c in 2000.
After putting the datafile to sQL 2008R2 everything worked fine for a while, the app used bravely the dbo schema, then people got messages from a newer version of the software "stored procedure exists in wrong schema. I checked the Db, and yes, there were about 20 stored procedures in schema "c" and about 40 in schema dbo and some fresh but unused tables.
Then I deleted the sp in schema "c",
issued the command
alter user [c] with default_schema=[dbo]
according to MS documentation the default schema is tried first, and when there's no default schema for the current user then name matching applies.
But the server behaved differently - then the app started to recreate the sp in the wrong schema again although I've assigned the dbo schema to be the default for this "c" username. And, before you ask, that user has db_owner privileges.
I've deleted the sp, then deleted the schema "c"
then I deleted the schema c
Then things went relly weird:
The application created the missing sp in the proper schema, but then issued a message "invalid pointer: the sp exists in a different schema"
We've checked the source code that generates the SP creation code and typed the code in a management studio window, and the result was as expected. The code works but between the app and the sql server some black magic seems to happen.
So the sp was in the proper schema, and how can it be created, but then being "hidden" from an user with db_owner?
Do I have to commit any transactions with this "alter user"? Usually the MS SQL Server does autocommit when commands are issued in the query window.
January 22, 2013 at 2:56 pm
those look like application generated errors rather than SQL ones.
Does the app refer to all objects by schema.name or just name? Sounds like the new version of the application is inconsistent in object ownership, or perhaps the fact the database has been ugraded fro 2000 was not taken into account.
In SQL2000 objects were always searched for by username.object first, then dbo.object, in SQL2005 up that can be bypassed by setting the default schema to dbo, giving a performance improvement, however if all objects are owned by the user or all or some are owned by user and object not qualified with
owner in SQL then default_schema should be left at user. Only testing would prove this.
---------------------------------------------------------------------
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply