We had a bit of problem in Microsoft SQL Server – Management Studio – v2008.
Though we had granted read permissions to one of our users via:
create user [lab\myGoodUser] from login [lab\myGoodUser]; exec sp_addrolemember [db_datareader], [lab\myGoodUser];
But, still the user upon logging on could not see any of the database objects. Looked everywhere, but no luck. So what to do:
- Kicked off “Microsoft SQL Server Profiler”
- Filtered for that user
- And, captured the sql generated when we refreshed the database object list
The Query we captured looks like:
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
Tried to understand the query, but it was not as easy. So what to do – Take the Easy way – Google….
SQL SERVER Management Studio 2008 can’t list all tables under SQL2005 database
http://www.sqlservercentral.com/Forums/Topic882092-391-4.aspx
In a nutshell:
- It seems that Management SQL Server Management Studio v2008 has problems when connecting to a v2005 database Instance
- The problem can be fixed by granting access to ‘view definition’
To grant access to ‘View Definition’
1) Grant ‘View Definition’ to specific object
–grant view definition on all database objects
use [msdb]
GRANT VIEW DEFINITION ON object::[dbo].[DTA_input] TO [lab\mygoodUser];
2) Grant ‘View Definition’ to specific schemas
use [myDB];
–http://msdn.microsoft.com/en-us/library/ms187940.aspx
–grant ability to view schema definition
–GRANT VIEW DEFINITION ON SCHEMA::<schema> TO [login];
GRANT VIEW DEFINITION ON SCHEMA::dbo TO [lab\myGoodUser];
3) Grant ‘View Definition’ to specific database
–http://msdn.microsoft.com/en-us/library/ms173848.aspx
–grant ability to view database definition
–GRANT VIEW DEFINITION ON DATABASE::<database> TO [login];
GRANT VIEW DEFINITION ON DATABASE::[myDB] TO [lab\mygoodUser];
4) Grant ‘View Definition’ to current database
–GRANT VIEW DEFINITION TO [login];
GRANT VIEW DEFINITION TO [lab\mygoodUser];
5) Grant ‘View Definition’ on all databases
–grant view definition on all database objects
GRANT VIEW ANY DEFINITION TO [lab\mygoodUser];
The problem appears to be with sys.tables.
Data returned from this table is governed based on user’s permission granted via ‘view definition’.
To validate issue query ( select * form sys.tables) against sys.tables and results will vary depending on permissions.
The error will not be a hard-error \ fault, but a bit more subtle.
More data:
- The problem is confirmed in MS SQL Server v2005 Engine when accessed via MS SQL Server – v2008 – Management Studio
- There is a MS Connect Article that is created:
Tables Node Does Not Show All Schemas In SSMSE 2008 by Nicholas Piasecki
https://connect.microsoft.com/SQLServer/feedback/details/387616/tables-node-does-not-show-all-schemas-in-ssmse-2008
