Schema specific object access in sql server is not happening after restoring backup

Question

I have an application which is accessing sql objects(stored procs/tables) etc which are defined under a schema 'A' for example A.my_stored_proc.

This application is working fine as intended when I connect it with the test server database. But, now as the client has asked to copy the db to into a local sql server dev edition for further enhancements.

I took backup of the db from the test server and restored it back to dev system. Now when I change the connection string of the application, the application throws an error saying that unable to located 'A.my_stored_proc'

I decided to manually test all the schema, user roles and rights against the test server which I found all are same.

For testing purpose I renamed the stored proc with 'dbo' schema and the application started working.

Can someone help me with this problem of schema with sql server 2008 r2?

score 0 · Answer 1 · answered Sep 11 '15 at 05:18

0

On the test server, the user account under which you application works probably has A as its default schema, yet the procedure is called without schema name mentioned:

exec my_stored_proc;

On the dev server, your user account may have dbo as its default schema (or it might be the owner of the restored database, which in this particular case will lead to the same behaviour).

Consult with SQL Profiler traces to determine how procedure calls look like, exactly.

answered Sep 11 '15 at 05:18

Roger Wolf

7,307
2
24
33

The most error are because developers use `SELECT *`, `EXEC name` and rely on many implicit mechanisms. – Lukasz Szozda Sep 11 '15 at 05:21
The user which is accessing the database has A has its default schema on both test server and local dev pc. I used the following query to assign ALTER USER DbUser WITH DEFAULT_SCHEMA = A; – Isaiyavan Babu Karan Sep 11 '15 at 05:58
On the dev pc, is this user also a member of `db_owner` or server `sysadmin` roles, by chance? – Roger Wolf Sep 11 '15 at 06:02
yes @RogerWolf and its there in the test server too. – Isaiyavan Babu Karan Sep 11 '15 at 06:07
@IsaiyavanBabuKaran, in that case default schema will not work - `sysadmin` members always use `dbo` as a default, regardless of anything. Here, for example: http://stackoverflow.com/questions/3806245/sql-server-schema-and-default-schema – Roger Wolf Sep 11 '15 at 06:10
@IsaiyavanBabuKaran, apparently I was wrong about `db_owner` members, they appear to be respectful of the user default schema. – Roger Wolf Sep 11 '15 at 06:12
@RogerWolf i will remove the db_owner rights and update. – Isaiyavan Babu Karan Sep 11 '15 at 06:23
@RogerWolf this is not helping – Isaiyavan Babu Karan Sep 11 '15 at 08:47
@RogerWolf i did the following and it worked. The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo. – Isaiyavan Babu Karan Sep 11 '15 at 09:06

Schema specific object access in sql server is not happening after restoring backup

1 Answers1