CODEWITHSUNDEEP
sql-serverssms
Leon
Leon

Reputation: 3244

How to prevent user to see databases not owned by him in SQL Server management studio?

When my SSMS (SQL Server management studio) is connected to my shared hosting db server, all databases including those not owned by me are listed in the left pane( the explorer of SSMS).

Ideally, I want only databases owned by me to be shown here. To display others' database is a security leak, though I have not permission to view their data. At least I know the db names of them.

Is it possible to achieve this, either by configuring my SSMS, or asking the support team of the service provider to change their settings ( I think it should be configured at server side)?

Thanks.

Upvotes: 1

Views: 599

Answers (2)

Leon
Leon

Reputation: 3244

I figured this out by running below SQLs.

DENY VIEW any DATABASE TO PUBLIC;
GRANT CREATE DATABASE TO PUBLIC;
ALTER AUTHORIZATION ON DATABASE::testdb TO testuser;

Sample

Upvotes: 0

Nate S.
Nate S.

Reputation: 1157

You can achieve this through schema permissions. Check out this thread for more information.

https://dba.stackexchange.com/questions/53509/setting-user-permissions-for-different-sql-server-schemas

Upvotes: 1

Related Questions