2

I want to create a Windows authentication server login and make it cannot see any system databases e.g.master, model.

I know that we can control view definition in database level e.g. database user, database role but I don't want to configure them to all system databases.

Therefore, I started by:

  1. Create Windows authentication login
  2. Create custom server role
  3. Deny view definition to a custom server role
  4. Add an login to a role

I hope the login shouldn't see all system databases but it still can see master and tempdb under System Databases node.

This is the screenshot of the result.

enter image description here

and these are my SQL statements.

-- Create login from Windows User
USE MASTER
GO

CREATE LOGIN [domain\web_user] FROM WINDOWS WITH 
DEFAULT_DATABASE = MyTestDB
GO

USE MyTestDB
GO

CREATE USER web_user FOR LOGIN [domain\web_user]
WITH DEFAULT_SCHEMA = dbo 
GO

EXEC sp_addrolemember @rolename = 'DB_DataReader', 
@membername = 'web_user'
GO



--Create custom server role, deny view definition and add login to role
USE master
GO

CREATE SERVER ROLE junior
GO 

DENY VIEW ANY DEFINITION TO [junior]
GO

ALTER SERVER ROLE junior ADD MEMBER [domain\web_user] ​
GO
  • 1
    Why do you want to prevent users seeing master and tempdb? If they aren't members of any server level roles, they won't be able to actually modify anything in master, and they'll only be able to see they're own items in tempdb. – Max Vernon Jul 10 '19 at 13:16
  • Take a look at CONTAINED database users (2012+) – Jonathan Fite Jul 10 '19 at 13:32
  • @MaxVernon Thanks for your comment. Actually the login I created is member of public server role by default. It can't modify master database for sure. However, I am curious if we can complete hide system database from this login and later it can see only its database. Thanks. – theeranitp Jul 10 '19 at 14:49
  • @JonathanFite Thanks for your comment. I am taken a look now. – theeranitp Jul 10 '19 at 14:50
  • I just found a good post I will test if it fix the issue I have. dba.stackexchange.com/questions/85868/… – theeranitp Jul 10 '19 at 15:11
0

From this post hide system databases sql server management studio.

I think it solves my problem. However, for normal login and user, we can't hide master and tempdb database.

The following information are in this document. hide system databases sql server management studio

After this permission is denied, a login can see only metadata for master, tempdb, and databases that the login owns.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.