SQL Server Login with view to only one database

Question

SQL Server Login with view to only one database

SSinhg 316

Hi

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

I want the user to connect to instance - Done
And have read write access to only 1 schema - Done
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

SQL

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

SSinhg 316

i have tried this approach as well but it doesn't show any database on SSMS

USE Mydatbase;
 GO -- Map the login to a user in the ISFAL database
CREATE USER rc_User FOR LOGIN rcACC; GO  -- Grant CONNECT permission to allow connection to this database

GRANT CONNECT TO rc_User;
 GO  -- Grant db_datareader role so they can view tables and data

EXEC sp_addrolemember 'db_datareader', rc_User; 
GO

Answer accepted by question author

1 additional answer

Your answer

SSinhg 316 Reputation points

2024-11-08T10:13:27.6733333+00:00

i have tried this approach as well but it doesn't show any database on SSMS

USE Mydatbase; GO -- Map the login to a user in the ISFAL database CREATE USER rc_User FOR LOGIN rcACC; GO -- Grant CONNECT permission to allow connection to this database GRANT CONNECT TO rc_User; GO -- Grant db_datareader role so they can view tables and data EXEC sp_addrolemember 'db_datareader', rc_User; GO

Answer 1

LiHongMSFT-4306 31,616

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".

SSinhg 316 Reputation points

2024-11-14T23:47:38.15+00:00

Thanks @lihongmsft

I don't think this can be achieved.
I followed the steps on StackOverflow.
Problem when deleting the user - database owner changes to DBO and then granting the newly created user access to a specific schema become a impossible task.

Not how others solved this problem...

Answer 2

Olaf Helper 47,586

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

SSinhg 316 Reputation points

2024-11-08T11:28:21.28+00:00

Thanks @Olaf Helper so I can grant to view only 1 database?
Olaf Helper 47,586 Reputation points

2024-11-08T13:00:39.2566667+00:00

The user can "see" only that one database he is connected to (and has permission for)

Share via

SQL Server Login with view to only one database

1 additional answer

Your answer