How to block access to remote databases

Question

How to block access to remote databases

martin 34304

SSC Rookie

Points: 45
More actions
December 1, 2015 at 11:01 am

#306683

I have the following configuration:
ComputerOne:
- SQL Server 2008 R2 Express
- Windows Authorization (user ComputerOne\User1)
- Databases: DbA, DbB, DbC and DbD
ComputerTwo:
- SQL Server 2008 R2 Express
- Windows Authorization (user ComputerTwo\User2)
- Database: DbX
ComputerTwo has access to ComputerOne (and ComputerOne's SQL instance) and I wish it to be able to access ONLY DbC (not DbA, DbB or DbD). I don't mind if it 'sees' the other remote databases, but I don't want it to be able to do *anything* with them)
How can I achieve this?

Viewing 8 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Give the login that's used rights to access only the DB that it should be able to see and no other permissions. By default a login has no permissions at all, you grant just the permissions that it needs for the person using it to do their job and no more.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

martin 34304 SSC Rookie Points: 45 More actions · Answer 2

GilaMonster (12/1/2015)
Give the login that's used rights to access only the DB that it should be able to see and no other permissions. By default a login has no permissions at all, you grant just the permissions that it needs for the person using it to do their job and no more.

How do I tell the ComputerOne to deny permissiones to a user on ComputerTwo? (remember I'm using Windows Authentication)

Can you give me some pointers as to what I should Deny?

Thanks,

Martin.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

I said nothing about deny, and it's nothing to do with the computers being used.

A login to SQL Server starts with no permissions. So take the login that's being used (you need to check your setup and see what logins are being used to access the instances) and grant that login just access to the DB that you want the user of that login to have access to.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

martin 34304 SSC Rookie Points: 45 More actions · Answer 4

GilaMonster (12/1/2015)
I said nothing about deny, and it's nothing to do with the computers being used.
A login to SQL Server starts with no permissions. So take the login that's being used (you need to check your setup and see what logins are being used to access the instances) and grant that login just access to the DB that you want the user of that login to have access to.

Very true, Deny was not mentioned, I was winging it!

Ok, so I grant access to the particular login being used. Can you tell me how to find out which login is being used?

Thanks (again!)

Martin.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

Check the application being used to access SQL and see what it's logging in as. If it's Windows Authentication, then it'll be something like <domain>\<logged in user> or <computer>\<logged in user>

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

martin 34304 SSC Rookie Points: 45 More actions · Answer 6

Go it!

I'll do the tests tomorrow and post the results here.

Much appreciated!

martin 34304 SSC Rookie Points: 45 More actions · Answer 7

martin 34304

SSC Rookie

Points: 45

December 3, 2015 at 5:09 am

#1843730

That worked just fine, thanks Gail.