SSMS - Revoking VIEW ANY DATABASE from Public Role by Phil Brammer

Active

Type:	Bug
ID:	774373
Opened:	12/14/2012 8:51:27 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

Revoking the permission, VIEW ANY DATABASE, from the Public server role causes SSMS to show only tempdb and master, even if a login has access to one or more databases.

For what it is worth, the query SSMS is running against the DB correctly returns the database(s) the login has access to, but SSMS is not showing it.

Details (expand)

English

SQL Server 2012 - Enterprise Edition

Tools (SSMS, Agent, Profiler, Migration, etc.)

Windows 7 Enterprise

US English

USE master
GO

-- ensure the Public server role can see all databases
GRANT VIEW ANY DATABASE to Public;
GO

-- create a new database
CREATE DATABASE I_Should_See_This
GO

-- create a new login
CREATE LOGIN dbSpecificLogin WITH PASSWORD = '1234', CHECK_POLICY = OFF, DEFAULT_DATABASE = I_Should_See_This;
GO

USE I_Should_See_This
GO

-- create database user in I_Should_See_This
CREATE USER dbSpecificUser FOR LOGIN dbSpecificLogin;
GO

-- in Object Explorer, connect to this instance with the new login, dbSpecificLogin
-- You should see all databases, including the database, I_Should_See_This

-- Now revoke 'VIEW ANY DATABASE' from the Public server role
USE master;
GO

REVOKE VIEW ANY DATABASE FROM Public;
GO

-- Now refresh your Object Explorer database listing
-- Note now that I_Should_See_This DB is not in the list - only tempdb and master.

-- Create a New Query in a new window, connecting as 'dbSpecificLogin'
-- Note that you correctly connect to the I_Should_See_This database
-- and the database drop-down list shows three databases correctly,
-- master, tempdb, and I_Should_See_This

-- The problem here is that query windows show databases correctly,
-- while SSMS' Object Explorer does not.

-- reset things back to the way they were; make sure dbSpecificLogin has logged out
DROP DATABASE I_Should_See_This;
GO

DROP LOGIN dbSpecificLogin;
GO

GRANT VIEW ANY DATABASE TO Public;
GO

The I_Should_See_This database does not show in Object Explorer when connected as the login, dbSpecificLogin, which has appropriate permissions to see the database.

The I_Should_See_This database shoud show in Object Explorer when connected as the login, dbSpecificLogin, which has appropriate permissions to see the database.

X64

0 attachments

Microsoft