Hide databases from windows group or restricting access through SSMS

Question

Hide databases from windows group or restricting access through SSMS

mobrien.gm

SSC Enthusiast

Points: 157
More actions
March 23, 2016 at 9:36 am

#312253

Hello,
We're using Server Server 2012 and we have a request for database access for a windows security group. There are approximately 75 databases on the server and we'd like for them to only see the one database (db_datareader) they have access to in SSMS. I've been able to accomplish restricting access by doing this:
-- group login
CREATE LOGIN [Domain\Group] FROM WINDOWS WITH
DEFAULT_DATABASE=DefaultDB
-- deny access
USE master;
GO
DENY VIEW ANY DATABASE TO [Domain\Group];
I'd like to add access to the default database now. I've read we could possibly do this:
USE master;
GO
ALTER AUTHORIZATION ON DATABASE::DefaultDB TO [Domain\Group];
GO
-- then
USE DefaultDB
GO
EXEC sp_addrolemember 'db_datareader', '[Domain\Group]'
I'm getting this error when trying to alter authorization using ALTER AUTHORIZATION ON DATABASE::DefaultDB TO [Domain\Group]:
"An entity of type database cannot be owned by a role, a group, an approle, or by principals mapped to certificates or asymmetric keys."
I've seen similar topics, but can't find the resolution. Has anyone had experience with this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I'm pretty sure you don't want to make that domain group the owner of the database anyway. That would be the same thing as putting all those users in the db_owner group in the database.

I haven't done this, but I believe if you remove the domain group from the public server role and then just give GRANT VIEW DEFAULTDB TO [Domain\Group] you'll accomplish what you want. The only issue is that they won't have permissions in the system databases that come with the public group.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

mobrien.gm SSC Enthusiast Points: 157 More actions · Answer 2

Thanks for your reply Jack, I appreciate it. Good catch, you're right I don't want to make that group owner of the database. Researching how to remove the domain group from the public server role now (it won't let you uncheck in SSMS). I'll follow up if I have any luck. Thanks again.

Jon.Morisi SSChampion Points: 12846 More actions · Answer 3

Here's the only way I've ever seen that done, but it doesn't address db_datareader only or the group ownership

USE [master]

GO

CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

USE [TestDB]

GO

--DON'T Create the DB User

--CREATE USER [test] FOR LOGIN [Test]

--GO

--USE [TestDB]

--GO

--ALTER ROLE [db_datareader] ADD MEMBER [test]

--GO

USE [master]

GO

DENY VIEW ANY DATABASE TO [test];

--USER BECOMES DBOWNER

USE [TestDB]

ALTER AUTHORIZATION ON DATABASE::[TestDB] to [test]

I'd be interested to know if there's a solution for this...

mobrien.gm SSC Enthusiast Points: 157 More actions · Answer 4

Thanks Jon, appreciate it, that's what I've seen as well. There are ALOT of posts/information about the topic, but I haven't resolved it yet (outside of db_owner). Another option I've seen mentioned is stripping access off the public role, and handling security through a different role. I've started looking at it, but haven't been able to focus on it since yesterday.