Mgmt Studio displays databases login doesn''t have access to
-
In early 2005, a minor fix was released for SQL Server 2000 that restricted the list of databases displayed in Enterprise Manager to those that the logged in user actually had access to. Generally this meant their one or two databases as well as master and tempdb (via Public) would be visible and no others. The fix was a replacement for sp_MSdbuseraccess.
Now, it seems that in Management Studio this is no longer the case and all databases are displayed, regardless of access.
I know that this isn't a security measure. It just makes my end users happier to see only their databases and no one else's. It also helps the list to load faster. This was a great fix for me in 2000, why would it have been removed? I wonder if the change hit 2000 too late for an addition to 2005...
Anyone know anything about it?
Here is the original fix: http://support.microsoft.com/default.aspx/kb/889696
Sincerely,
Dan B
-
The fix will not be applicable because of two reasons.
1. In SQL 2005 there is no single procedure fires in background (i captured Profiler trace)
2. you cannot update system objects in SQL 2005.
The fix in SQL 2000 was to resolve slow performance of Enterprise Manager not to make users "Happy"
-
Thanks for the reply! Here is what I saw in a trace. Seems to be the same for Mgmt Studio regardless of connecting to a 2000 or 2005 server.
Note the lack of has_dbaccess() in the Management Studio. Is this query generated and executed from a procedure? If so, can't it be changed?
-Dan B
--From Enterprise manager
select name, DATABASEPROPERTY(name, N'IsDetached'),
(case when DATABASEPROPERTY(name, N'IsShutdown') is null then -1 else DATABASEPROPERTY(name, N'IsShutdown') end),
DATABASEPROPERTY(name, N'IsSuspect'),
DATABASEPROPERTY(name, N'IsOffline'),
DATABASEPROPERTY(name, N'IsInLoad'),
(case when DATABASEPROPERTY(name, N'IsInRecovery') is null then -1 else DATABASEPROPERTY(name, N'IsInRecovery') end),
(case when DATABASEPROPERTY(name, N'IsNotRecovered') is null then -1 else DATABASEPROPERTY(name, N'IsNotRecovered') end),
DATABASEPROPERTY(name, N'IsEmergencyMode'),
DATABASEPROPERTY(name, N'IsInStandBy'),
has_dbaccess(name), status, category, status2
from master.dbo.sysdatabases
--***********************************************************
--From Management Studio
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
-
from Enterprise Manager:
===================
exec sp_MSdbuseraccess N'db', N'%'
and then
select name, DATABASEPROPERTY(name, N'IsDetached'), (case when DATABASEPROPERTY(name, N'IsShutdown') is null then -1 else DATABASEPROPERTY(name, N'IsShutdown') end),
DATABASEPROPERTY(name, N'IsSuspect'), DATABASEPROPERTY(name, N'IsOffline'), DATABASEPROPERTY(name, N'IsInLoad'), (case when DATABASEPROPERTY(name, N'IsInRecovery') is
null then -1 else DATABASEPROPERTY(name, N'IsInRecovery') end), (case when DATABASEPROPERTY(name, N'IsNotRecovered') is null then -1 else DATABASEPROPERTY(name,
N'IsNotRecovered') end), DATABASEPROPERTY(name, N'IsEmergencyMode'), DATABASEPROPERTY(name, N'IsInStandBy'), has_dbaccess(name), status, category, status2 from
master.dbo.sysdatabases
Above code is to show the status of database in SEM
This is not the case with management studio.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply