Is it possible to Grant View to user(with Read Only permission) to only one Database in MS SQL Server
Is it possible to create a database user in MS Sql Server with readonly role for one specific database (for example TestDataBase) among hundreds of databases and when logged on with that user to the Sql Server, the user can see only that specific database (TestDataBase)? This is in concern with the security perspective as sometimes database names itself gives out lot of information.
Yes. When you create the user (hopefully it's a windows user or group and NOT an SQL Server user), right click on "mapping" and follow your nose. This is a pretty basic question and if you're going to be the one to maintain security, I strongly urge you to get a copy of "Books Online" and start reading about this and other security related issues so that we don't hear about your company getting hacked on TV.
If you are concerned about the database names, I have 2 suggestions. My first would be to not use database names that should be kept secret. If you feel you must (or cannot change that now), you can deny a given login (individual or group) the ability to view databases. By default, the public server role can see the names of all databases, but a DENY overrides that GRANT. If denied, the user can only see master, tempdb, and any database he or she owns. Take note of that last part. Even if the user has some level of access in another database, that database will only be visible (when querying sys.databases, for instance) if he/she is an owner of it. DENY VIEW ANY DATABASE TO [DeniedUser]; The following example gives a user read access to everything in a given database, but the user cannot see any other databases on the server instance. In fact, if that user is connected to master and queries sys.databases, he/she won't even see the 1 database he/she has access to but queries against that database will still work. USE master; DENY VIEW ANY DATABASE TO [DeniedUser]; USE [AllowedDatabase]; CREATE USER [DeniedUser] FROM LOGIN [DeniedUser]; ALTER ROLE db_datareader ADD MEMBER DeniedUser; If you have a version older than SQL Server 2012, replace the "ALTER ROLE" statement with the following: EXEC sp_addrolemember 'db_datareader', 'DeniedUser';