1

I created a new login and a new user and I mapped a database in SQL Server 2012. In the login properties in Securables, we selected permissions "deny" for "View any database" for the user to see only the specified database.

The problem is that the user does not see any database.

If I select "Grant" to "View any database", user can see all databases. How can I make the user can see only the specified database?

migrated from stackoverflow.com Oct 24 '13 at 13:51

This question came from our site for professional and enthusiast programmers.

2

According to these two articles you need to make the login the owner of the database as well as denying the "View any database" permission for that login:

USE [master];
GO
ALTER AUTHORIZATION ON DATABASE::database_name TO [login];
GO
2

Please do this after denying access to other database .. i think newely created user's default database is MASTER you need to give him newley created db as default database. you can do this by following steps

1) go to SECURITY >> LOGINS >> Select USER >> RIGHT CLICK on it >> Select PROPERTIES >> Select you newley created Database in DEFAULT DATABASE

Please see below screenshot for the reference

Assign default database

  • This won't prevent the user from seeing the other databases, which is what the OP wants. – bob esponja Oct 24 '13 at 9:22
  • @bobesponja .. i was editing my answer .. what generally happens that new user's default database has not been changed (so the default database is master) .. and the user have been denied access of master database so that user will not see anything – Dhaval Oct 24 '13 at 9:26
  • Setting the default schema and denying "View All" will prevent the user from seeing any databases in the object explorer, they have to be given the dbowner role for the database to appear. It may have been fixed in SQL Server 2012 though, I'm not able to check. edit: I tried undoing the downvote but it's locked until an edit is made to your answer. – bob esponja Oct 24 '13 at 9:36
  • i dont mind with downvotes but this happened to me once so i have given the answer.. – Dhaval Oct 24 '13 at 9:38
  • Yeah, my bad for the downvote, it didn't occur to me it might be fixed in SQL Server 2012. Edit your answer and I'll remove the downvote. – bob esponja Oct 24 '13 at 9:52

Your Answer

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