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?
Deny overrides the grant that the public user has by default to view any database. This is why your user can't see any databases. To solve your question - from [this MSDN page]: "The metadata that describes the master and tempdb databases is always visible to public. Members of the sysadmin fixed server role can always see all database metadata. Database owners can always see rows in sys.databases for databases that they own." "To limit visibility to database metadata, deny a login the VIEW ANY DATABASE permission. After this permission is denied, a login can see only metadata for master, tempdb, and databases that the login owns" This means that to get that user to view the database's metadata, they must be the database owner. In that case, I would suggest granting them the [VIEW DEFINITION] permission - although I have not tested this, and you may find that the deny at the server level will override the grant at the database level - in which case you would have to consider removing the grant from the public login, which would of course have implications for other users. From [that page]: "Database Scope VIEW DEFINITION granted at this scope effectively negates permissions-based metadata access for the grantee in the specified database. This means that the grantee can see all metadata in the database context in which the GRANT statement is executed, unless the grantee is denied VIEW DEFINITION or CONTROL permissions at the schema scope or for an individual entity such as a table. For information about the syntax to use for this permission at this scope, see GRANT (Transact-SQL)." :