SQL Server Login with view to only one database

Question

Hi

I have a request to create a SQL account on my Instance of SQL 2014

1. I want the user to connect to instance - Done
2. And have read write access to only 1 schema - Done
3. view only 1 database - Not working out

How i can i restrict user to see only 1 database and not all.

Below code is generated from CHATGPT and it works for step 1 & 2

USE master;
GO
-- Step 1: Create a login for the user if not already created
CREATE LOGIN [NewUserLogin] WITH PASSWORD = '';
GO
-- Step 2: Create a user in the ISFAL database for this login
USE mydatabse;
GO
CREATE USER [NewUser] FOR LOGIN [NewUserLogin];
GO
-- Step 3: Grant CONNECT permission to make visible and accessible
GRANT CONNECT TO [NewUser];
GO
-- Step 4: Grant SELECT permission on a specific schema (e.g., 'SchemaName') in mydatabase
GRANT SELECT ON SCHEMA::SchemaName TO [NewUser];
GO
-- Step 5: Deny VIEW ANY DATABASE permission at the server level for the login
USE master;
GO
DENY VIEW ANY DATABASE TO [NewUserLogin];
GO

Answer 1

Hi @SSinhg

See this similar thread: Hide SQL database from Management Studio.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

By revoking the "View Any Databases" permissions, see

https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-server-permissions-transact-sql?view=sql-server-ver16