asked
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.
Thanks. I am a developer and not much experience with DBA roles and Admin. This is a requirement in creating a SQL Server user(not a Windows User) and we got a reply saying either we can grant view to all the databases(restricting the access to rest of the databases) or view none of the database(with read only access to one database) when logged in using that user. That's why I am trying to see if there is a solution to create a read only user in SQL Server Authentication mode to view and access one database among many.
This web site runs off of votes. Can you please indicate all helpful answers below by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that one answer.
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';
Thanks for the Suggestions. Since I want my user(DeniedUser) to be a READ ONLY and not an Owner of the Database, DENY VIEW ANY DATABASE TO [DeniedUser]; does not let me see the Database if I log in with DeniedUser. Trying to find if there is a way not to own a database but read values and not write to the database and view only one database that DeniedUser has Read Only rights for.
16 People are following this question.
