March 18, 2010 at 7:52 pm
I think Jeff hit it on the head here. SQL 2008 is more explicit in it's permissions than was SQL2005. With that, it is also more secure and thus is likely functioning as designed.
Yeah, that make sense ...
I was in conversation with our internal Microsoft dba/liaison, and he believes the same but has no provided any formal documentation about it, no yet. It is difficult to explain that to a regular John Doe user who usually work via GUI or SSMS and had that before ... now, not anymore ....
March 18, 2010 at 8:05 pm
sql-lover (3/18/2010)
I think Jeff hit it on the head here. SQL 2008 is more explicit in it's permissions than was SQL2005. With that, it is also more secure and thus is likely functioning as designed.
Yeah, that make sense ...
I was in conversation with our internal Microsoft dba/liaison, and he believes the same but has no provided any formal documentation about it, no yet. It is difficult to explain that to a regular John Doe user who usually work via GUI or SSMS and had that before ... now, not anymore ....
I believe it can somewhat be explained via the scripts that Peter Brinkhaus provided. SSMS 2008 queries the permissions from the 2005 database differently than does SSMS 2005.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 19, 2010 at 5:52 am
So SSMS for 2008 is not backward compatible. I understand what Peter had written but the point is we connect to both 2005 and 2008 databases using the SSMS for 2008. I have consultants in here that are being paide $$ per hour and we cannot get this to work. I've got to tell them to use something else for now I guess. Me personally, I'm using TOAD, so I'm not running into this but I have end users who are. Can anyone find something "official" from MS?
March 19, 2010 at 7:06 am
As Peter showed, the only difference is this line:
INNER JOIN sys.database_principals AS stbl ON stbl.principal_id = ISNULL(tbl.principal_id, (OBJECTPROPERTY(tbl.object_id, 'OwnerId')))
tbl.principal_id comes from sys.objects (inherited by sys.tables). Its value is described in Books Online as:
ID of the individual owner, if different from the schema owner. By default, schema-contained objects are owned by the schema owner. However, an alternate owner can be specified by using the ALTER AUTHORIZATION statement to change ownership. Is NULL if there is no alternate individual owner.
If it is NULL, the result of OBJECTPROPERTY(tbl.object_id, 'OwnerId')) is used.
I would therefore check that the OBJECTPROPERTY expression returns a valid database principal for your tables.
Especially if the database concerned was upgraded from SQL Server 2000.
The relationship between schemas and owners changed in 2005, and this often causes problems like this.
The easy fix is to use the ALTER AUTHORIZATION statement to ensure that all tables have a valid owner that makes sense in your permissions hierarchy.
Paul
March 19, 2010 at 9:29 am
It would be nice to see the outcome of an LEFT OUTER JOIN version of the query executed by SSMS 2008. If the Owner column is NULL there is a problem with the ownership of some tables.
Dan, sql_lover, can you execute this query in SSMS2008 and let us know the result?
exec sp_executesql N'SELECT
''Server[@Name='' + quotename(CAST(serverproperty(N''Servername'') AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(db_name(),'''''''') + '']'' + ''/Table[@Name='' + quotename(tbl.name,'''''''') + '' and @Schema='' + quotename(SCHEMA_NAME(tbl.schema_id),'''''''') + '']'' AS [Urn],
tbl.name AS [Name],
SCHEMA_NAME(tbl.schema_id) AS [Schema],
CAST(
case
when tbl.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = tbl.object_id and
minor_id = 0 and
class = 1 and
name = N''microsoft_database_tools_support'')
is not null then 1
else 0
end
AS bit) AS [IsSystemObject],
tbl.create_date AS [CreateDate],
stbl.name AS [Owner]
FROM
sys.tables AS tbl
LEFT OUTER JOIN sys.database_principals AS stbl ON stbl.principal_id = ISNULL(tbl.principal_id, (OBJECTPROPERTY(tbl.object_id, ''OwnerId'')))
WHERE
(CAST(
case
when tbl.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = tbl.object_id and
minor_id = 0 and
class = 1 and
name = N''microsoft_database_tools_support'')
is not null then 1
else 0
end
AS bit)=@_msparam_0)
ORDER BY
[Schema] ASC,[Name] ASC',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'0'
Peter
March 19, 2010 at 9:33 am
Good idea, Peter. Sorry if I stepped on your toes there - it just seemed like the thread has wandered away from the point you made earlier.
March 19, 2010 at 9:47 am
Paul White NZ (3/19/2010)
Good idea, Peter. Sorry if I stepped on your toes there...
Not at all. And I think it was a great suggestion about upgrading a SQL2000 database to SQL2005, although I don't have not much experience with it, at least not with schemas.
I was just wondering why nobody tried the outer join in the first place. I thought it was the obvious thing to do to see if ownership was the problem.
Peter
March 19, 2010 at 12:24 pm
This is exectued in ssms 2008 against a 2005 database.
Server[@Name='XXXX']/Database[@Name='master']/Table[@Name='DeadlockEvents' and @Schema='dbo'] DeadlockEvents dbo 0 2007-10-23 13:44:50.310 dbo
Server[@Name='XXXX']/Database[@Name='master']/Table[@Name='tuning' and @Schema='dbo'] tuning dbo 0 2008-02-01 10:59:53.460 dbo
Server[@Name='XXXX']/Database[@Name='master']/Table[@Name='AppRecruit' and @Schema='TT_online_app'] AppRecruit TT_online_app 0 2007-10-11 13:14:52.950 TT_online_app
(3 row(s) affected)
March 19, 2010 at 9:19 pm
Dan B (3/19/2010)
This is exectued in ssms 2008 against a 2005 database.Server[@Name='XXXX']/Database[@Name='master']/Table[@Name='DeadlockEvents' and @Schema='dbo'] DeadlockEvents dbo 0 2007-10-23 13:44:50.310 dbo
Server[@Name='XXXX']/Database[@Name='master']/Table[@Name='tuning' and @Schema='dbo'] tuning dbo 0 2008-02-01 10:59:53.460 dbo
Server[@Name='XXXX']/Database[@Name='master']/Table[@Name='AppRecruit' and @Schema='TT_online_app'] AppRecruit TT_online_app 0 2007-10-11 13:14:52.950 TT_online_app
(3 row(s) affected)
I don't understand, Dan :unsure:
March 20, 2010 at 12:02 am
Dan B (3/19/2010)
This is exectued in ssms 2008 against a 2005 database.Server[@Name='XXXX']/Database[@Name='master']/Table[@Name='DeadlockEvents' and @Schema='dbo'] DeadlockEvents dbo 0 2007-10-23 13:44:50.310 dbo
Server[@Name='XXXX']/Database[@Name='master']/Table[@Name='tuning' and @Schema='dbo'] tuning dbo 0 2008-02-01 10:59:53.460 dbo
Server[@Name='XXXX']/Database[@Name='master']/Table[@Name='AppRecruit' and @Schema='TT_online_app'] AppRecruit TT_online_app 0 2007-10-11 13:14:52.950 TT_online_app
(3 row(s) affected)
What query did you run to return this information? There were two queries provided (if it is one of the ones from this thread).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 6, 2010 at 7:48 am
I am having the same problem described here. I am upgrading from SQL 2000 to SQL 2005. It is a 3rd party app that uses a sql login who owns all the tables. I have developers that are trying to write reports and debug issues but they can not see the tables. I have given them db_datareader plus select, View Definitions on the schema but they still can only see dbo objects. It is getting critical that I resolve this.
Peter, I ran the query and the schema and object owners are the same user.
Thanks.
May 11, 2010 at 1:47 pm
Found that SQL 2008 SSMS must have a bug of some sort. User has right to select from the schema. In SSMS 2005 user can see all tables owned by the schema but in SSMS 2008 he can't even though the query runs correctly. We are having to have the users use the 2005 client vs the 2008 client on any databases that are 2005 and use schema other than dbo.
May 11, 2010 at 9:53 pm
I had similar kind of problem whereas i was not even able to see any databases in object explorer of SSMS2008 but quires were running fine, but this article helped me to resolve the issue hope that might help you as well.
Nag
Nag
------------------------------------------------
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
May 12, 2010 at 8:55 am
Actually my problem is only with schemas. I have a contact at MS researching the issue I will post here what he finds out. Here is what I found. The query I am referring to is posted earlier in this thread so I am not going to post it here.
I think I have found out why you cannot see schema owned objects other than dbo in SQL 2008 while you can in 2005. Below is the query that runs in SQL 2008 it has an inner join. In SQL 2005 it has a left join which allows all the objects to be seen when joined to sys.database_principals. But this is not the problem. The problem is that the user does not get back all the rows back from sys.database_principals. It does not return any rows for schemas except dbo unless you have a much higher permission level.
Set up:
Create a login xxx and add the login to a database. Create a table owned by the user xxx’s schema.
Create a role. Grant select on schema::xxx to the role
Create another login yyy and add the login to the database and to the new role.
Start SSMS 2005 using login yyy. Expand the database and it will show you the table owned by xxx.
Start SSMS 2008 using login yyy. Expand the database and it will not show you the table owned by xxx.
Select * from sys.database_principals with login yyy and you will not see the database_principal xxx so the table will not show up with the inner join but will with an outer join. I tried to look at the view sys.database_principals but there are some objects and functions that I can not access. These must reside in mssqlsystemresource which I guess I don’t have direct access to. The definition of the view can be found with:
SELECT object_definition(OBJECT_ID('sys.database_principals'))
The schema objects appear if a user is granted db_accessadmin, db_securityAdmin or db_owner.
HTH
May 27, 2010 at 1:11 pm
Did you ever find any further info about this issue? I believe we're having the same problem. Thanks!
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply