0

I am trying to make it so a test user can only see the dbs that he has access to. Currently, the user can only see the one db that he has access to, but he is now the db_owner of that database. I don't feel that a test user should be the db_owner as this gives him access to everything in the database. From the articles that I found online, this seems to be the only way to make it so the user can only see the single db. When I use DENY VIEW ANY DATABASE TO [user] he does not see any db within the server, including the db I have given explicit view privileges to. Is there a way to allow him to only view the single db without giving him db_owner privileges?

The article I am referencing is here.

3
  • You mean a test login, right? Can you please give us a hint about what version of SQL Server and Management Studio you are using? Feb 15 '18 at 1:07
  • @AaronBertrand SSMS 17.4 and SQL Server 2014. I am writing a c# application that will use the user that I created, but I would like to only see the single database that he has access to in SSMS. Does this make more sense?
    – kevorski
    Feb 15 '18 at 16:12
  • I know you have asked about SSMS, but a heads up with the newish Azure Data Studio. Once deny view any database is set, a user can add a connection to ADS with the target database specified and unlike SSMS, ADS will show the database and objects within the database for the user (and only those objects they have permissions on). They will need one connection per database setup, but once inside the any connection there is no restriction (e.g. you can perform cross database queries). Not perfect, but is a workaround.
    – Jayden
    Aug 19 at 22:01
5

First of all if you DENY VIEW ANY DATABASE TO [user] it will not "see" even the database where it's a memeber of db_owner role, except for sysadmin, only database owner will "see" the database owned by it.

Database owner is different from just membership in db_owner role, it's a login that has authorization on that database (grant authorization on database::myDB to myLogin), so only one login at a time can own the database, and the corresponding user has all the permissions in that database and can "see" it in OE even when VIEW ANY DATABASE is denied to the corresponding login.

Saying that there is an alternative solution to your problem, it's SQL Server 2012 Contained Database Feature

Starting with SQL Server 20012 you can enable Contained Database Feature at the instance level and then make your databases contained.

This means that there will be a database, not server to authenticate your users, and all these users will "see" only the databases where they were created. They will not "see" nothing at the server level.

If one day you'll need to migrate your databases to another server, you'll not need to transfer server logins to another server, your contained database already contains all its users and they are not "orphaned" because they don't have corresponding logins at all.

1
  • A contained user might be an option, but the OP asked about the databases, plural, so you'd have the additional management of multiple copies of the same user, their need to connect to each database independently, and their inability to do things like cross-database queries (or look at jobs, logins, linked servers, etc. if they also should have the ability to do any of those things). Feb 15 '18 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.