Limit database view based on logins

Question

Limit database view based on logins

Jaime I

SSC Journeyman

Points: 91
More actions
February 5, 2010 at 9:08 am

#229669

Hi there,
I have a situation where I want to limit the database view per user login.
For example: I have 3 databases in my SQL Server instance called MyDB, UserDB, RestrictedDB. I then create a 2 logins; User1 and User2. I then did a DENY VIEW ANY DATABASE TO [User1] and then DENY VIEW ANY DATABASE TO [User2]. I then made User1 the owner of UserDB by executing sp_changedbowner 'User1'
When I login as User1, I can only see UserDB which is exactly what I want. But of course when I login as User2, there is no database that he can view.
I made User2 as a db_owner as well by executing sp_addrolemember 'db_owner','User2' and logged in again but I still can't see UserDB. I did not attempt to execute sp_changedbowner on User2 as I think the result would be that User2 will be able to see UserDB but not User1.
What I need is for both User1 & User2 to be able to see just UserDB. How would I go about doing this?
Thanks in advance!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Eric_Gagne SSCrazy Points: 2062 More actions · Answer 1

I really don't know much on database security and permissions but while reading your post I wondered if adding user2 to the db_owner role is enough, I would try adding it to the db_datareader role as well.

Jaime I SSC Journeyman Points: 91 More actions · Answer 2

Gagne (2/5/2010)
I really don't know much on database security and permissions but while reading your post I wondered if adding user2 to the db_owner role is enough, I would try adding it to the db_datareader role as well.

Thanks for the reply.

I added User2 to the db_owner role but I guess that was not enough. I would try adding User2 to the db_datareader role and see if that will do the trick.

Thanks again.

Jaime I SSC Journeyman Points: 91 More actions · Answer 3

Jaime I (2/8/2010)
Gagne (2/5/2010)
I really don't know much on database security and permissions but while reading your post I wondered if adding user2 to the db_owner role is enough, I would try adding it to the db_datareader role as well.
Thanks for the reply.
I added User2 to the db_owner role but I guess that was not enough. I would try adding User2 to the db_datareader role and see if that will do the trick.
Thanks again.

No dice. Added db_datareader. Same result.

Andrew Watson-478275 SSCarpal Tunnel Points: 4652 More actions · Answer 4

If you check DENY in BOL, it says

Denies a permission to a principal. Prevents that principal from inheriting the permission through its group or role memberships

which is why changing the group membership of User2 isn't having any effect - deny trumps any group permissions.

I'm not clear on why you need to use DENY VIEW ANY. As long as these users don't have some kind of system level access that gives them access, you shouldn't need this (unless you don't even want them to know that the databases are there).

Jaime I SSC Journeyman Points: 91 More actions · Answer 5

Andrew Watson-478275 (2/9/2010)
If you check DENY in BOL, it says
Denies a permission to a principal. Prevents that principal from inheriting the permission through its group or role memberships
which is why changing the group membership of User2 isn't having any effect - deny trumps any group permissions.
I'm not clear on why you need to use DENY VIEW ANY. As long as these users don't have some kind of system level access that gives them access, you shouldn't need this (unless you don't even want them to know that the databases are there).

Fair enough. I guess the DENY VIEW ANY was a lazy way of preventing User1 from viewing the other databases since initially, there was only 1 user that has access to the particular database. But now, another user was added that should have the same behavior as the first user.

What do you recommend on how to go about this exercise of preventing users from viewing databases that they're not suppose to view?

Thanks for the reply and I'm looking forward to your suggestion.

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 6

When you create the logins, using the dialog box (not from Query Editor), under user mappings, you can select the database(s) which a user should be able to access, you can choose individual databases to each user and remember not to give any Server level roles..

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Andrew Watson-478275 SSCarpal Tunnel Points: 4652 More actions · Answer 7

It depends on how much you want to restrict things by. DENY VIEW is only needed if you don't even want the users to know that the databases are there.

If you only want to stop them seeing the data, then you don't need to do anything like this - as long as you're not setting them up as admins, then you would use GRANT (or use SSMS/EM as Bru says) to assign them only the permissions they need.

P Jones SSChampion Points: 12361 More actions · Answer 8

I create a database role to limit user access within a database - for one troublesome user in particular their role only allows read on just one view in just one database! Then the user is given that role through Management Studio and security, logins and user mapping as above.

The next thing is that someone else needs the same permissions and it's easy to give them the same role.

Using Management Studio is much more straightforward than T-SQL code. Create the role by selecting security, roles in the database and the user by selecting security, logins within the instance.

Jaime I SSC Journeyman Points: 91 More actions · Answer 9

The instance hosts multiple databases for different clients. Because of a privacy policy, other clients should not be able to view the other databases except their own.

In the example above, both Users will act as db_owner. The thing is when User2 connects to the server via Management Studio, he does not see the database that was assigned to him. All I want to do is that User2 when he connects via Management Studio, is to see or view his database.

If it is so easy to DENY VIEW of all the databases to a user, why is it not as straight forward to let a user VIEW a particular database? Is there not a way to just GRANT VIEW to a particular database to a user?

sihaab SSCommitted Points: 1742 More actions · Answer 10

This is a known issue with SSMS, many DBA struggling to answer database owners with this question. Only way to resolve this issue is to avoid using SSMS for this situation. I propose to TOAD for SQL Server to a client, who share my instance with other clients. I think that solved the issue.

Otherwise, try using

ALTER AUTHORIZATION ON DATABASE::<db> to <login>

but only one user can see the objects, if there is other users in the database, they cannot see objects.

I don't know why Microsoft did not find a solution to this.