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:
- Create Windows authentication login
- Create custom server role
- Deny view definition to a custom server role
- 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.
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