This is one of the most common view what we expect once we have granted access to a particular login and we need to perform the below steps to achieve the same as Microsoft has yet to release a fix and a login having access to only 1 database has the view of all the databases present in the environment.
******************************** DB Script ***********************************
USE [master]
GO
-- make sure they can view all databases for the moment.
GRANT VIEW ANY DATABASE TO DBUser
GO
USE DBName
go
-- drop the user in the database if it already exists.
IF EXISTS (SELECT *
FROM sys.database_principals
WHERE name = N'DBUser')
DROP USER DBUser
GO
-- grant them ownership to of the database (ownership of dbo schema).
ALTER AUTHORIZATION ON DATABASE::DBName to DBUser
go
USE MASTER
go
-- deny ability to see ohter databases
DENY VIEW ANY DATABASE TO DBUser
go
********************************END***************************************
SSMS View after access restrictions :
No comments:
Post a Comment