3

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!

Jaime
  • 141
  • 2
  • 3

3 Answers3

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).

mrdenny
  • 27,074
  • 4
  • 40
  • 68
  • 1
    Thanks for the reply mrdenny. The requirement is that for the user to see only the database he is assigned to. I already have a similar user that is doing just that but I cannot replicate the functionality. The person in charge is away on vacation and I have never tried to do this before. It's possible that there are a couple of more steps that I need to do like prevent the user to view the other databases. – Jaime Feb 02 '10 at 21:37
0

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.

Community
  • 1
Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47
  • Hi Nick, Thanks for the reply. I was able to deny view of any of the database to the user but for the life of me I can't find an example as to how to grant a user to view the specific database. If I had a user TheUser and the database is MyDatabase, what would be the syntax in granting him view? Thanks a lot! – Jaime Feb 03 '10 at 17:24
0

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
djangofan
  • 4,172
  • 10
  • 45
  • 59