| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2014-06-26 : 15:23:09
|
| Hi,There is this nuisance I couldn't figure out.In one of our SSMS environment, I can successfully runselect * from SchemaName.tbl_name but not Select * from tbl_nameIt got Invalid object name error.My login default schema was set to that schema. Isn't that all it take to take care of that?And, other developers can run scripts without the schema name. Whenever I run their copy of code, I have to insert many schema in front of table names. Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-26 : 15:32:02
|
| I don't have the answer, but the other developers need to include the schema name in their scripts. All stored procedures/queries/scripts should be 2-part naming convention. We have a strict rule here about that.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2014-06-26 : 15:55:30
|
But there must be a way to skip that part, so how do you reinforce that rule? quote:
Originally posted by tkizer
I don't have the answer, but the other developers need to include the schema name in their scripts. All stored procedures/queries/scripts should be 2-part naming convention. We have a strict rule here about that.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-26 : 15:58:58
|
quote:
Originally posted by Hommer
But there must be a way to skip that part, so how do you reinforce that rule? quote:
Originally posted by tkizer
I don't have the answer, but the other developers need to include the schema name in their scripts. All stored procedures/queries/scripts should be 2-part naming convention. We have a strict rule here about that.
We have code reviews. If the 2-part name isn't used, it doesn't get approved which means it has to be modified in order for it to be deployed.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-06-26 : 16:24:18
|
| If your login is a member of the sysadmin fixed server role, your default schema is ingored; SQL Server searches the dbo schema as default in that case. That could be what is happening to you. |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2014-06-26 : 16:42:14
|
Interesting. So I can't have both. thanks!quote:
Originally posted by James K
If your login is a member of the sysadmin fixed server role, your default schema is ingored; SQL Server searches the dbo schema as default in that case. That could be what is happening to you.
|
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-06-26 : 16:50:44
|
| It is just that you can have only one default schema. For sysadmins the default schema setting is ignored and the default is assumed to be dbo. This page has a blurb about it and about how SQL Server decides which schema to search. http://msdn.microsoft.com/en-us/library/ms176060.aspx |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2014-06-26 : 17:50:53
|
| Just like I can run a script as another user by:Execute As is there a way to start a query session and force it to be under certain schema instead of the default?The reason I want to do this is that from time to time I have to run big chunk of vender supply script and each time I have to add schema to many places. Maybe I should create a sql login with a none sysadmin role then it will not default to dbo. |
|
|
|
|
schema nuisance