Wednesday, March 7, 2012

How can I hide mssql server system database?

Dear All,

I m using mssql server 2005, when i create a user account for mssql and then use this account to login via management studio. It will show all the system databases and other client databases. How can i hide those databases and allow the client to see their own databases.

Regards,

Ricky

You can hide all databases (other than system databases) by revoking VIEW ANY DATABASE permission from public server role. But you cannot tweak Management Studio to only show certain databases.

Thanks
Laurentiu

|||

Hi Laurentiu

Could you tell me the basic steps (sql statements ) that can allow the clients only its related databases. Because i use a sa account to run "DENY VIEW ANY DEFINITION TO public", the result is incorrect.

Regards,

Ricky

|||

Hello,

In order to see who all have access to 'View Any Database' Permission, you can use the following query

SELECT l.name as grantee_name, p.state_desc, p.permission_name
FROM sys.server_permissions AS p JOIN sys.server_principals AS l
ON p.grantee_principal_id = l.principal_id
WHERE permission_name = 'VIEW ANY DATABASE' ;

and to DENY access to public you can use the following syntax

DENY VIEW ANY DATABASE TO PUBLIC

|||

The problem is that what are the related databases for a client is not an easy to answer question: whether a user can or cannot access the database cannot be determined without accessing the database. Testing the user access for all databases will not be efficient if you have hundreds of databases.

If you want to filter databases within SSMS, I don't think there is a way to do that, but the Tools forum is a more appropriate place to get guidance on this issue.

If you want to create your own view that the client can query to see the databases that he has access, you can filter sys.databases using has_dbaccess:

select name from sys.databases where has_dbaccess(name) = 1

Thanks
Laurentiu

No comments:

Post a Comment