On my server that is hosting SQL Server 2008 R2, I open SSMS and under Security -> Logins there is a login named "SomeLoginName". When I log in to the server with this login, I am able to see all of the databases on the server.
I would like to restrict this user to only see 2 of the databases that are on the server. I've seen some solutions that say to revoke the VIEW ANY DATABASE
permission for the login and then add the login as the db_owner
for the databases I want "SomeLoginName" to be able to see. I don't want to have "SomeLoginName" as the db_owner
for the databases that it is supposed to see.
Is there a way that I can configure "SomeLoginName" to only see 2 databases on the server without "SomeLoginName" being the db_owner
for these 2 databases?
Thanks in advance.
Thanks to @sepupic , his/her answer is correct. It turns out that I actually am running MS SQL Server 2012 so I was able to implement the Contained Database concept. The steps listed on the linked pages in @sepupic 's answer didn't work for me though. I found this one and put this script together. Here's what it does:
'contained database authentication'
to 1 for the MS SQL Server instanceRECONFIGURE
Here's the script:
USE master;
GO;
EXEC sp_configure 'contained database authentication', 1;
GO;
RECONFIGURE;
GO;
CREATE DATABASE ContainedDB2
CONTAINMENT = PARTIAL;
GO;
USE ContainedDB2;
GO;
CREATE USER cduser2
WITH PASSWORD = N'Pa$$word',
DEFAULT_SCHEMA = dbo;
GO;
Then you just configure the connection to the contained database in the section that begins with
Login and Verify the User Permissions on a Contained Database
Using the script I put together and configuring the connection under the section I mentioned sets it up so you connect to the server with the user that is created and that user can only see the contained database(s) you want it to. You have to configure the user to have permissions like the db role db_datareader
in the contained database but instructions on how to do these types of things are easy to come by if you search for them. Thanks again to @sepupic for getting me started on coming up with an answer.