deny all access - but there's a gotcha

Question

deny all access - but there's a gotcha

StillaLearner

SSCrazy

Points: 2479
More actions
September 17, 2008 at 3:31 pm

#197877

Hi All,
I have come up against a situation I haven't run into before.
I have setup a DataWarehouse Level 1 (source data) SQL Server 2000 and 2005 installations.
The source L1 sql server databases are either being logged shipped - so in read only mode or read only\standby mode.
The idea is that this will be a data repository for a SAS DW and all users will be using EG or some sort of SAS tool to get at the L2 data - however some of them will get access to the L1 data (SQL Server)
I have been asked to make sure that no one except two domain level accounts have read privs on the L1 data.
I can remove the public access, but I won't be able to handle the read only databases.
Is there another way for me to accomplish this?
OR is it just that simple - remove public access and just add the two domain accounts?
thanks

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

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Yes. If someone hasn't been granted permission to connect to SQL Server i.e. they're login hasn't been added to the SQL Server, they can't see anything. You wouldn't even have to worry about the database level if the only logins allowed to connect are the two domain logins.

Greg

StillaLearner SSCrazy Points: 2479 More actions · Answer 2

thanks Greg, but in our situation I am being led to believe its a little more complicated than that.

The SQL Server 2000 and 2005 instance is on a server in a doamin. Our organisation is a multi domain with no trusts between them.

The only users still specified on the SQL Server instance is builtin\administrator. NT System, sa and the AD group that needs access.

A collegue who has an account on another domain cannot connect to the server\instance - which is what I had expected. However if she uses the fully qualified domain name and an account on the same domain that the SQL Server instance is in she can get through even though she is not specified on the sql server instance in the logins - this I didn't expect. She is not a member of any of the groups already specified.

So why is this? is it because the account is a valid domain account?, the instance is specified for mixed authentication. OR is it because of public?

thanks

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

I think she must be a member of one of the Windows groups. If she can see the contents of a database, it's because that group is a member of the Public database role. By default, Public can see objects in every database but can't access data.

Greg