December 15, 2010 at 6:04 pm
I hope this is in the right forum.
I am migrating data into a database called prodDB. The application that uses prodDB always connects as a user called vtd whose default schema is vtd and all tables in prodDB are in the vtd schema.
I insert records in prodDB with code like...
INSERT INTO prodDB.vtd.someProdTable SELECT field1, field2.. from MyTable
However, there is a trigger on someProdTable with code like...
INSERT INTO someAuditTable SELECT field1, field2.. from INSERTED
The table someAuditTable belongs in the schema vtd, and so the trigger is assuming the user's default schema is vtd
The trigger fails for me because it is expecting...
INSERT INTO vtd.someAuditTable SELECT field1, field2.. from INSERTED
On my development server I have a copy of prodDB and have created a schema called vtd. My login has vtd as the default schema for prodDB, so I thought that the trigger will now fire correctly. But it doesn't. I get the error...
Invalid object name 'someAuditTable'
(If I change the trigger to include the schema name it works fine, but this is not an option because (a) I can't change the production DB and (b) there are literally hundreds of them.)
What do I need to do?
TIA
December 16, 2010 at 9:44 pm
I found what the problem was. My login had sysadmin role.
This is from BOL...
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.
Works fine after removing sysadmin from my login.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply