Home / server / Questions / 108889
In Process
Jaime
Asked: 2010-02-03 11:20:25 +0800 CST

SQL Server User Mapping - Limit view of databases for a user

  • 772

I am adding a new Login with SQL Server Authentication. I set its Server Role as public and then went into User Mapping, selecting the only database this user should have access to. I then change the Default Schema to dbo and made this user the db_owner.

I then connect to the instance using the new user's credentials and I can see not only the database he should have access to but all the other attached databases.

How can I limit this user to just see the database he has access to?

Thanks in advance!

sql-server user-settings

3 Answers

  1. To do that you would need to remove the guest account for all the other databases. While the user can see the other databases, he can't open them and do anything (unless the guest account or the public role within those databases have access to anything).

    • 1

  2. Check out the VIEW ANY DATABASE server level permission. The public role is granted this permission by default. You can revoke it from public & then grant it specifically to logins you want to allow. Alternatively, leave the default grant on public and deny the permission to logins you do not want to see all databases.

    Check out this answer for the syntax.

    Using the deny isn't exactly the alternative. Doing this will mean the login can never see any databases listed, even if the login has permission to them.

    • 0

  3. Addendum to Nick Kavadias answer: Not sure if this is what you need, but does this work?

    USE AdventureWorks 
GO 
GRANT VIEW MyDatabase TO User1
    • 0