Need VIEW DEFINITION permissions per database


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