C# / .NETDevOpsMisc
C# / .NET
SQL Server hide database list from a particular user
Alexandru Puiu
Alexandru Puiu
November 05, 2015
1 min

One of the things I’ve been searching for is to hide the list of databases a user doesn’t have access to. By default, when a user connects to SQL Server, especially a shared instance, he sees all the databases available on the server, even though he doesn’t have access to view or access any of them. The goal is to only show a user the database(s) he should see. This isn’t fully supported yet by SQL, but there’s a way to solve the problem for the majority of cases. First, you need to deny the ability for a user to view any databases he does not own.

use master GO DENY VIEW ANY DATABASE TO [myuser]; GO

Secondly, you need to ensure the user is the owner of each database he needs access to. This is the part that’s the workaround, since the user can still access all the databases he’s been assigned permissions for, but only databases he owns show up in the Object Explorer. To change the database owner, you can run the following:

use [MyDatabase] GO EXEC sp_changedbowner ‘myuser’ GO


Tags

sql
Alexandru Puiu

Alexandru Puiu

Engineer / Security Architect

Systems Engineering advocate, Software Engineer, Security Architect / Researcher, SQL/NoSQL DBA, and Certified Scrum Master with a passion for Distributed Systems, AI and IoT..

Expertise

.NET
RavenDB
Kubernetes

Social Media

githubtwitterwebsite

Related Posts

Shrink Transaction Log
DevOps
MSSQL Shrink / Truncate transaction log
March 10, 2019
1 min

Subscribe To My Newsletter

I'll only send worthwhile content I think you'll want, less than once a month, and promise to never spam or sell your information!
© 2023, All Rights Reserved.

Quick Links

Get In TouchAbout Me

Social Media