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