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?

Improve this question
0

2 Answers 2

Reset to default
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

Improve this answer
9
  • This won't prevent the user from seeing the other databases, which is what the OP wants.
    – bob esponja
    Commented Oct 24, 2013 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
    Commented Oct 24, 2013 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
    Commented Oct 24, 2013 at 9:36
  • i dont mind with downvotes but this happened to me once so i have given the answer..
    – Dhaval
    Commented Oct 24, 2013 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
    Commented Oct 24, 2013 at 9:52
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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.