March 31, 2009 at 11:38 am
[font="Courier New"]Please help me understand why 5th query fails although PACRAT is default schema for pacrat user of [Dev:PacratPatterns] database (see sp_helpuser...). Pay attention to the last two sp_help calls.
select '(1) - ' + suser_name()
--------------------------------------------------------------------------------------------------------------------------------------
(1) - pacrat
(1 row(s) affected)
select '(2) - ' + db_name()
--------------------------------------------------------------------------------------------------------------------------------------
(2) - Dev:PacratPatterns
(1 row(s) affected)
select '(3.1) - ' + table_name from information_schema.tables where table_type='view' and table_name ='tv' union
select '(3.2) - ' + table_schema from information_schema.tables where table_type='view' and table_name ='tv'
----------------------------------------------------------------------------------------------------------------------------------------
(3.1) - tv
(3.2) - PACRAT
(2 row(s) affected)
select '(4) - ' , * from pacrat.tv
d
------ -----------------------
(4) - 2009-03-31 12:26:29.503
(1 row(s) affected)
select '(5) - ' , * from tv
Msg 208, Level 16, State 1, Line 8
Invalid object name 'tv'.
sp_helpgroup 'db_owner'
Group_name Group_id Users_in_group Userid
------------------------- ----------- ------------------------- -----------
db_owner 16384 dbo 1
db_owner 16384 IgorM 5
db_owner 16384 dba 6
db_owner 16384 DMZ\Administrator 7
db_owner 16384 pacrat 8
db_owner 16384 pacratadmin 9
(6 row(s) affected)
sp_helpuser 'pacrat'
UserName GroupName LoginName DefDBName DefSchemaName UserID
------------ ---------------- --------- --------- ------------- ----------
pacrat db_owner NULL NULL PACRAT 8 0x205D4780F2F8A0488A5940C221487F86[/font]
March 31, 2009 at 12:04 pm
Hi,
Just a thought at a very higher level..
select '(5) - ' , * from tv
by default, tv will be searched under 'dbo' schema if we dont mention the schema explicitly.
---------------------------------------------------------------------------------
March 31, 2009 at 12:40 pm
... unless the user has "Default Schema" name defined that is different from "dbo". It is "pacrat" as you can see from the very last query results and from this one:
select db_name()
-------------------
Dev:PacratPatterns
(1 row(s) affected)
select name, default_schema_name
from sys.database_principals u
where u.name='pacrat'
-------------------
Dev:PacratPatterns
(1 row(s) affected)
name default_schema_name
------------------- -------------------
pacrat PACRAT
(1 row(s) affected)
March 31, 2009 at 1:08 pm
Is you database case sensitive ?
If you enter a default schema for a user, it will nolonger search the way it did in sql2000 !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 31, 2009 at 1:30 pm
select name, default_schema_name
from sys.database_principals u
where u.name='pacrat' and u.name='PacRaT' and u.name='PACRAT'
name default_schema_name
------ ----------------------
pacrat PACRAT
(1 row(s) affected)
March 31, 2009 at 1:50 pm
Hi,
Sorry not a solution.. in case if it rings any bell to anyone who looks at this post..
BOL defn of default schema on SQL Server 2000 and SQL Server 2005
SQL Server 2005 also introduces the notion of "default schema", which is used to resolve the names of objects that are referred to without their fully-qualified names. In SQL Server 2000, the location first checked is the schema owned by the calling database user, followed by the schema owned by DBO. In SQL Server 2005, each user has a default schema, which specifies the first schema that will be searched by the server when it resolves the names of objects. The default schema can be set and changed using the DEFAULT_SCHEMA option of CREATE USER and ALTER USER. If DEFAULT_SCHEMA is left undefined, the database user will have DBO as its default schema.
I just created a schema, an object under that and defined the schema as default schema for an user that I am logging in as.. Its giving the same error for me as well.."Invalid object Name".
Just curious to know what actually is happening!
---------------------------------------------------------------------------------
April 1, 2009 at 6:11 am
It doesn't look like it from the code you showed, but are you using qualified names in the queries?
SELECT *
FROM schema.table
--not
SELECT *
FROM table
That would absolutely explain the problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2009 at 7:11 am
The problem is that there is a ton of code that uses just simple object name without schema name. And I want on one hand somehow improve DB organization with schemas, and on the other hand to avoid any recoding in stored procedures and in C# code.
So far I could not find better solution than this one 🙁
CREATE SYNONYM [dbo].[tv] FOR [Dev:PacratPatterns].[pacrat].[tv]
select * from tv
d
-----------------------
2009-04-01 08:52:37.077
(1 row(s) affected)
I still don't believe in so egregious bug may exist in SQL Server 2005.
April 1, 2009 at 7:19 am
Regardless of default schema, if you don't reference the schema in the query, SQL Server will try to find the query in dbo. I don't think it's a bug. It's well known and documented behavior. If you don't want to recode the old stuff, you might not be able to use schema's.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2009 at 7:31 am
BOL claims:
"In SQL Server 2005, each user has a default schema, which specifies the first schema that will be searched by the server when it resolves the names of objects. The default schema can be set and changed using the DEFAULT_SCHEMA option of CREATE USER and ALTER USER."
April 1, 2009 at 7:47 am
Maybe I'm wrong. Wouldn't be the first time (today). Hang on, testing... might be a while.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 2, 2009 at 12:46 am
It depends !
With an upgraded database, the default schema will be the userid.
This script needs to be executed with sysadmin auth because of the "execute as" and "Revert".
Don't forget to execute the "/* cleanup after tests */" part !
-- Testcode
create database dSSCTest
go
use dSSCTest
go
Create schema lSSCTest;
go
create login lSSCTest WITH PASSWORD = 'StrongP@ssw0rd', DEFAULT_DATABASE = dSSCTest;
go
create user lSSCTest for login lSSCTest;
go
EXEC sp_addrolemember N'db_datareader', N'lSSCTest'
go
set nocount on
go
Create table T1 (col1 int identity(1,1) not null, col2 datetime not null default getdate())
go
insert into T1 default values
go 1000
-- select * from T1;
Create table lSSCTest.T1(TheFrist int identity(1,1) not null, TheSecond datetime not null default getdate())
go
insert into lSSCTest.T1 default values
go 1000
-- select * from lSSCTest.T1;
SELECT USER_ID() AS [USER_ID]
, USER_NAME() AS [USER_NAME]
, SUSER_ID() AS [SUSER_ID]
, SUSER_SNAME() AS [SUSER_SNAME]
, IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin]
, IS_MEMBER('db_owner') AS [Is_DB_owner]
, IS_MEMBER('db_datareader') AS [Is_DB_Datareader]
, ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN]
GO
EXEC AS USER = 'lSSCTest';
go
SELECT USER_ID() AS [USER_ID]
, USER_NAME() AS [USER_NAME]
, SUSER_ID() AS [SUSER_ID]
, SUSER_SNAME() AS [SUSER_SNAME]
, IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin]
, IS_MEMBER('db_owner') AS [Is_DB_owner]
, IS_MEMBER('db_datareader') AS [Is_DB_Datareader]
, ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN]
GO
Select * from T1
go
Select * from lSSCTest.T1
go
REVERT;
go
SELECT USER_ID() AS [USER_ID]
, USER_NAME() AS [USER_NAME]
, SUSER_ID() AS [SUSER_ID]
, SUSER_SNAME() AS [SUSER_SNAME]
, IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin]
, IS_MEMBER('db_owner') AS [Is_DB_owner]
, IS_MEMBER('db_datareader') AS [Is_DB_Datareader]
, ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN]
go
/* Set the default schema */
ALTER USER [lSSCTest] WITH DEFAULT_SCHEMA=[lSSCTest]
go
EXEC AS USER = 'lSSCTest';
go
SELECT USER_ID() AS [USER_ID]
, USER_NAME() AS [USER_NAME]
, SUSER_ID() AS [SUSER_ID]
, SUSER_SNAME() AS [SUSER_SNAME]
, IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin]
, IS_MEMBER('db_owner') AS [Is_DB_owner]
, IS_MEMBER('db_datareader') AS [Is_DB_Datareader]
, ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN]
GO
Select * from T1
go
Select * from lSSCTest.T1
go
REVERT;
go
SELECT USER_ID() AS [USER_ID]
, USER_NAME() AS [USER_NAME]
, SUSER_ID() AS [SUSER_ID]
, SUSER_SNAME() AS [SUSER_SNAME]
, IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin]
, IS_MEMBER('db_owner') AS [Is_DB_owner]
, IS_MEMBER('db_datareader') AS [Is_DB_Datareader]
, ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN]
go
/* drop the table with the default schema */
drop table lSSCTest.T1
go
EXEC AS USER = 'lSSCTest';
go
SELECT USER_ID() AS [USER_ID]
, USER_NAME() AS [USER_NAME]
, SUSER_ID() AS [SUSER_ID]
, SUSER_SNAME() AS [SUSER_SNAME]
, IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin]
, IS_MEMBER('db_owner') AS [Is_DB_owner]
, IS_MEMBER('db_datareader') AS [Is_DB_Datareader]
, ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN]
GO
Select * from T1
go
-- nolonger exists ! Select * from lSSCTest.T1
go
REVERT;
go
SELECT USER_ID() AS [USER_ID]
, USER_NAME() AS [USER_NAME]
, SUSER_ID() AS [SUSER_ID]
, SUSER_SNAME() AS [SUSER_SNAME]
, IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin]
, IS_MEMBER('db_owner') AS [Is_DB_owner]
, IS_MEMBER('db_datareader') AS [Is_DB_Datareader]
, ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN]
go
/* cleanup after tests */
--use master
--go
--drop database dSSCTest;
--go
--drop login lSSCTest;
--go
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 3, 2009 at 12:54 pm
Excellent code, ALZDBA, thanks.
Could you please help me with my problem:
Given:
Login: pacrat
User: pacrat with default schema: [pacrat]
view pacrat.tv (as select getDate())
BOL states that under these circumstances query 5 in my code that initiated this thread, should work.
Required:
Take actions to make it work as BOL promises.
What are these actions please?
April 5, 2009 at 11:55 pm
Igor Makedon (4/3/2009)
Excellent code, ALZDBA, thanks.Could you please help me with my problem:
Given:
Login: pacrat
User: pacrat with default schema: [pacrat]
view pacrat.tv (as select getDate())
BOL states that under these circumstances query 5 in my code that initiated this thread, should work.
Required:
Take actions to make it work as BOL promises.
What are these actions please?
Is login pacrat by any chance member of the sysadmin sqlserver role ??
In that case I think there is no option to define a default schema. It will always use dbo !
I'll test it and keep you posted.
-- edited --
As I assumed, the default schema isn't taken into account for members of the sysadmin server role !
They always default to dbo.
Just a little addition to the previous test script...
-- now make the user sysadmin
EXEC master..sp_addsrvrolemember @loginame = N'lSSCTest', @rolename = N'sysadmin'
go
/* be aware by using Exec as USER it doesn't check the server level permissions */
EXEC AS USER = 'lSSCTest';
go
SELECT USER_ID() AS [USER_ID]
, USER_NAME() AS [USER_NAME]
, SUSER_ID() AS [SUSER_ID]
, SUSER_SNAME() AS [SUSER_SNAME]
, IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin]
, IS_MEMBER('db_owner') AS [Is_DB_owner]
, IS_MEMBER('db_datareader') AS [Is_DB_Datareader]
, ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN]
GO
Select * from T1
go
REVERT;
go
/* for a bit of magic ! */
/* By using Exec as LOGIN it does check the server level permissions */
EXEC AS LOGIN = 'lSSCTest';
-- *****
go
SELECT USER_ID() AS [USER_ID]
, USER_NAME() AS [USER_NAME]
, SUSER_ID() AS [SUSER_ID]
, SUSER_SNAME() AS [SUSER_SNAME]
, IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin]
, IS_MEMBER('db_owner') AS [Is_DB_owner]
, IS_MEMBER('db_datareader') AS [Is_DB_Datareader]
, ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN]
GO
Select * from T1
go
REVERT;
go
SELECT USER_ID() AS [USER_ID]
, USER_NAME() AS [USER_NAME]
, SUSER_ID() AS [SUSER_ID]
, SUSER_SNAME() AS [SUSER_SNAME]
, IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin]
, IS_MEMBER('db_owner') AS [Is_DB_owner]
, IS_MEMBER('db_datareader') AS [Is_DB_Datareader]
, ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN]
go
-- remove sysadmin for the login
EXEC master..sp_dropsrvrolemember @loginame = N'lSSCTest', @rolename = N'sysadmin'
go
/* put the cleanup code overhere */
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply