March 12, 2010 at 1:10 pm
Hi,
I wonder if this is a bug or new behavior or what (please take a look on my screen-shoot) but when I connect to a SQL2005 instance with SSMS2008 using a regular user with limited privileges, I can not list all tables under a database. Now, If I connect to same SQL2005 instance with SSMS2005, using same user, against same database, I can.
March 13, 2010 at 6:50 am
I'd report it to Microsoft. I've never seen that behavior and I've been using SSMS 2008 since the beta to manage 2000 and 2005 databases without any issues (except the known ones in 2000).
"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
March 13, 2010 at 7:33 pm
iamthemanx (3/12/2010)
Hi,I wonder if this is a bug or new behavior or what (please take a look on my screen-shoot) but when I connect to a SQL2005 instance with SSMS2008 using a regular user with limited privileges, I can not list all tables under a database. Now, If I connect to same SQL2005 instance with SSMS2005, using same user, against same database, I can.
Did you try this with other login say, any SA role login?
March 13, 2010 at 7:42 pm
GTR (3/13/2010)
iamthemanx (3/12/2010)
Hi,I wonder if this is a bug or new behavior or what (please take a look on my screen-shoot) but when I connect to a SQL2005 instance with SSMS2008 using a regular user with limited privileges, I can not list all tables under a database. Now, If I connect to same SQL2005 instance with SSMS2005, using same user, against same database, I can.
Did you try this with other login say, any SA role login?
Yes, with my account, which is part of "sysadmin" server role. I have no issues, I'm able to see all tables.
March 13, 2010 at 10:33 pm
iamthemanx (3/13/2010)
GTR (3/13/2010)
iamthemanx (3/12/2010)
Hi,I wonder if this is a bug or new behavior or what (please take a look on my screen-shoot) but when I connect to a SQL2005 instance with SSMS2008 using a regular user with limited privileges, I can not list all tables under a database. Now, If I connect to same SQL2005 instance with SSMS2005, using same user, against same database, I can.
Did you try this with other login say, any SA role login?
Yes, with my account, which is part of "sysadmin" server role. I have no issues, I'm able to see all tables.
Then it is problem with user account permission, make sure you clone the security of the user.
March 14, 2010 at 3:51 am
Then it is problem with user account permission, make sure you clone the security of the user.
Hi,
Thanks for reply.
But maybe you did not read my post very well. The screenshoot or problem is from the same SQL instance. I'm connecting to the same database but using different SSMS versions. It is not, a permission issue because it works when using SSMS2005. It is something with the SSMS2008 client console ... something changed that now, does not display all tables properly.
There is nothing to clone because it is the same SQL database all the time.
It could be a permission issue if we were talking about two different SQL instances, but it is the same... so, permissions are the same too.
March 14, 2010 at 4:26 am
If I open the Tables list in SSMS 2005 the following query is fired:
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]
FROM
sys.tables AS tbl
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)=0)
ORDER BY
[Schema] ASC,[Name] ASC
If I open the same Table list (same database, same SQL 2005 instance) from SSMS 2008 this query is executed:
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
INNER 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'
In SSMS 2008 there is an INNER JOIN between sys.tables and sys.database_principals. So it may have something to do with permissions/ownership. Check out sys.database_principals on your system.
Peter
March 14, 2010 at 5:46 am
That's interesting, never thought on check the script(s) behind.
I know for sure is a security stuff, the way SSMS2008 is reading that from the SQL2005 instance. But why I should check a missing or broken permission at database or server level, if that works on SSMS2005, got my point? It is a security issue but at SSMS2008 level, in my opinion ... or the way it is currently retrieving the medatada from the server.
BTW, a workaround, is adding VIEW DEFINITION to user, but I don't want that ...
March 14, 2010 at 10:12 am
iamthemanx (3/14/2010)
That's interesting, never thought on check the script(s) behind.I know for sure is a security stuff, the way SSMS2008 is reading that from the SQL2005 instance. But why I should check a missing or broken permission at database or server level, if that works on SSMS2005, got my point? It is a security issue but at SSMS2008 level, in my opinion ... or the way it is currently retrieving the medatada from the server.
BTW, a workaround, is adding VIEW DEFINITION to user, but I don't want that ...
Just a guess on my part, but I'm thinking that this user does not have permissions on those tables that do not show up in the SSMS2008 client tools. Using the 2005 client tools, the user can see everything - but they don't have access to everything. Using the 2008 client tools - the user can only see what they have permissions for.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 16, 2010 at 12:12 pm
Is the ssms2008 tool on the same computer as the ssms2005 tool? Or a different box?
Because if it's a different box, you may be losing your user authentication crossing the box boundaries. The views you are querying would - by design - respond differently in that case.
Been there, done that, got the bloody forehead from banging my head against the wall until I understood...
March 16, 2010 at 12:53 pm
david_wendelken (3/16/2010)
Is the ssms2008 tool on the same computer as the ssms2005 tool? Or a different box?Because if it's a different box, you may be losing your user authentication crossing the box boundaries. The views you are querying would - by design - respond differently in that case.
Been there, done that, got the bloody forehead from banging my head against the wall until I understood...
Thanks for reply.
The behavior is the same, trying locally or from different computers. But I do not believe that could be an issue. When you open SSMS you must put your credentials so wherever you are connecting or running the SSMS2005/2008 client, is transparent. Remember, what you can see, change or alter, depends of your local security settings, not from where you are connecting. And your local settings or what is displayed, comes from the credentials or SQL login you put when opening SSMS on your laptop or computer.
March 18, 2010 at 1:13 pm
We are running into the same problem. Create a user and grant them db_datareader. They'll not be able to see all the tables. We can see some of the dbo.* tables and thats it. This is using ssms. Use Visual Studio and you can see everything. Looks to be a bug in the management studio.
March 18, 2010 at 1:26 pm
May be you discovered another bug in SQL 2008 SSMS.
March 18, 2010 at 4:39 pm
Jeffrey Williams-493691 (3/14/2010)
iamthemanx (3/14/2010)
That's interesting, never thought on check the script(s) behind.I know for sure is a security stuff, the way SSMS2008 is reading that from the SQL2005 instance. But why I should check a missing or broken permission at database or server level, if that works on SSMS2005, got my point? It is a security issue but at SSMS2008 level, in my opinion ... or the way it is currently retrieving the medatada from the server.
BTW, a workaround, is adding VIEW DEFINITION to user, but I don't want that ...
Just a guess on my part, but I'm thinking that this user does not have permissions on those tables that do not show up in the SSMS2008 client tools. Using the 2005 client tools, the user can see everything - but they don't have access to everything. Using the 2008 client tools - the user can only see what they have permissions for.
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.
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 18, 2010 at 7:42 pm
It's not a sql2008 db. It's only mgmt studio connecting to sql2005
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply