Hi,
i'm working on the rtm version of mssql 2005 and i'm wondering to find that the database list are hard coded into SQL Server Management.
I need to hide databases list for (newbie) users on EM 2000 i can hide this list using this kb http://support.microsoft.com/?id=889696 but on the new SQLSM
The database list is hard coded into this batch query
SELECT
dtb.name AS [Database_Name],
'Server[@.Name=' + quotename(CAST(serverproperty(N'Servername') AS sysname),'''') + ']' + '/Database[@.Name=' + quotename(dtb.name,'''') + ']'
AS [Database_Urn],
case
-- if all these are false then we are in the Normal state
-- except some return NULL if it's AutoClosed
when (DATABASEPROPERTY(dtb.name,'IsInLoad') = 0 and
(DATABASEPROPERTY(dtb.name,'IsInRecovery') = 0 or DATABASEPROPERTY(dtb.name,'IsInRecovery') is null) and
(DATABASEPROPERTY(dtb.name,'IsNotRecovered') = 0 or DATABASEPROPERTY(dtb.name,'IsNotRecovered') is null) and
DATABASEPROPERTY(dtb.name,'IsSuspect') = 0 and
DATABASEPROPERTY(dtb.name,'IsOffline') = 0 and
DATABASEPROPERTY(dtb.name,'IsInStandBy') = 0 and
(DATABASEPROPERTY(dtb.name,'IsShutDown') = 0 or DATABASEPROPERTY(dtb.name,'IsShutDown') is null) and
DATABASEPROPERTY(dtb.name,'IsEmergencyMode') = 0) then 1
else 0
end |
case
when DATABASEPROPERTY(dtb.name,'IsInLoad') = 1 then 2
else 0
end |
case
when DATABASEPROPERTY(dtb.name,'IsInRecovery') = 1 and
DATABASEPROPERTY(dtb.name,'IsNotRecovered') = 1 then 4
else 0
end |
case
when DATABASEPROPERTY(dtb.name,'IsInRecovery') = 1 then 8
else 0
end |
case
when DATABASEPROPERTY(dtb.name,'IsSuspect') = 1 then 16
else 0
end |
case
when DATABASEPROPERTY(dtb.name,'IsOffline') = 1 then 32
else 0
end |
case
when DATABASEPROPERTY(dtb.name,'IsInStandBy') = 1 then 64
else 0
end |
case
when DATABASEPROPERTY(dtb.name,'IsShutDown') = 1 then 128
when DATABASEPROPERTY(dtb.name,'IsShutDown') is null then (512 + 128)
else 0
end |
case
when DATABASEPROPERTY(dtb.name,'IsEmergencyMode') = 1 then 256
else 0
end
AS [Database_Status],
dtb.cmptlevel AS [Database_CompatibilityLevel],
CASE DATABASEPROPERTYEX(dtb.name, 'Recovery') WHEN 'SIMPLE' THEN 3 WHEN 'BULK_LOGGED' THEN 2 ELSE /*FULL*/ 1 END AS [RecoveryModel],
CASE CONVERT(sysname,DATABASEPROPERTYEX(dtb.name, 'UserAccess')) WHEN 'SINGLE_USER' THEN 1 WHEN 'RESTRICTED_USER' THEN 2 ELSE /*MULTI_USER*/
0 END AS [UserAccess],
CAST(DATABASEPROPERTY(dtb.name, 'IsReadOnly') AS bit) AS [ReadOnly],
dtb.name AS [Database_DatabaseName2]
FROM
master.dbo.sysdatabases AS dtb
WHERE
(CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else category & 16 end AS bit)=0)
ORDER BY
[Database_Name] ASC
So my question it's possible to have a stored procedure on the next fix of mssql 2005 with a stored procedure on the master database to show the database list, i know that the database are listed on the table master.dbo.sysdatabases but i need to hide database for newbie users.
If your question is related to Management Studio, please post to
SQL Server Tools General.
If you don't want a user to be able to see all entries of sys.databases/sysdatabases, then you can just deny VIEW ANY DATABASE permission to them. By default, this permission is assigned to public (for backward compatibility with SQL Server 2000), so every principal has it.
Thanks
Laurentiu|||Hi Laurentiu,
yes the question is related to Management Studio and i'll post on the correct forum.
Thank's|||
Note: with SP2 client, DENY VIEW ANY DATABASE TO <login> results in NO, NONE, NATA databases other than master and tempdb being visible in object explorer. The problem is clearly that SSMS calls master.dbo.databases - if it would just call dbo.databases (using the login's default database) then all the databases the user has access to might be listed. I will check the other forum for help, but MS PM's- if you're listening- this is terribly annoying.
Yes, I have tried adding the logins as users in master, then granting select on the system view- deny view any database permission trumps it. Also, why is there no [GRANT/REVOKE/DENY] VIEW DATABASE::<dbname> syntax?!!!
Other than permissions, and their quirks such as this, SQL Server is a great product.
, I don't understand what you mean. If you think that I have a permission problem where should I look? Which permission and on what? How to give the proper rights if that is what's missing?