Block windows groups

Question

Block windows groups

Alren

Mr or Mrs. 500

Points: 575
More actions
October 11, 2008 at 3:31 pm

#199545

Hi,
for some administrative tasks we want to exclude particular users coming into the database via windows active directory groups.
Existing users related to that group should also be thrown out.
The administrative tasks are done with non admin rights.
I can't seem to find anything related to such a problem on the internet!:(
Has anyone ideas how to do this ?

Viewing 11 posts - 1 through 10 (of 10 total)

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

I don't quite understand. You want to allow the Windows groups to connect, but restrict them from certain adminstrative functions ? Can you give some examples ?

Alren Mr or Mrs. 500 Points: 575 More actions · Answer 2

I have users that access the database via windows groups.

I want to kill their sessions and avoid them to be able to log in again during the run of some DTSx (running under a different account).

The windows user the DTSx is running under don't need/have administrative rights.

This is why "alter database ... set restricted_users" is not an option.

Is there an way to achieve this without giving db_owner rights or similar to the DTSx users ?

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

You could disable the login, or remove permission to the specific database(s) so they can still access other databases.

Alren Mr or Mrs. 500 Points: 575 More actions · Answer 4

What do you call disable the login?

If it is "ALTER LOGIN ... DISABLE", I tried it but it doesn't seem to work for windows groups...:(

I have found a "DENY CONNECT SQL TO ..." that seems to work but it is quite dangerous since it is a DENY permission (unexpected lock outs).

I wanted to avoid to revoke permissions from the database since there are object level permissions (--> hard to maintain permissions) but you may be right!?

I was hoping for a magic bullet...

Thanks anyway! 😎

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

I don't know why the "disable" doesn't work ... I don't have a test account to try.

Maybe someone more knowledgeable can answer ?

What do you mean by unexpected lockouts making the DENY dangerous ?

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

IMO DENY is the way to go !

It will prevent users of the windows group from accessing your db.

Maybe users are member of more then one group, so they may still be able to login because of group membership of another group.

Deny would only work for that group, but not in the case I described.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Alren Mr or Mrs. 500 Points: 575 More actions · Answer 7

This would be great if the deny was restricted to the windows group but in my understanding this is what happens:

user A is part of windows groups G1 and G2.

G1 has a "grant connect SQL"

G2 has a "deny connect SQL"

This means that A has a grant AND a deny connect SQL.

This in turn means no access since the deny prevents connection.

But if I'm wrong, this would solve part of my problem.

The other part is still "How do I kill sessions based on their groups?"

Thanks for your answer 😎

Alren Mr or Mrs. 500 Points: 575 More actions · Answer 8

Alren (10/13/2008)
This would be great if the deny was restricted to the windows group but in my understanding this is what happens:
user A is part of windows groups G1 and G2.
G1 has a "grant connect SQL"
G2 has a "deny connect SQL"
This means that A has a grant AND a deny connect SQL.
This in turn means no access since the deny prevents connection.
But if I'm wrong, this would solve part of my problem.
The other part is still "How do I kill sessions based on their groups?"
Now say user A is a database administrator
G1 the DBA group (sysadmin rights)
G2 a user group able to select some records (in normal situation)
--> this is why I'm afraid of using denys
Thanks for your answer 😎

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 9

Alren (10/13/2008)[hr..Now say user A is a database administrator
G1 the DBA group (sysadmin rights)
G2 a user group able to select some records (in normal situation)
--> this is why I'm afraid of using denys
Thanks for your answer 😎

[/quote]

Well ... sysadmin is omnipotent in sqlserver.

I haven't tested deny login with that, but if I'm correct that will not work for sysadmin members.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

bpflanzer SSC-Addicted Points: 448 More actions · Answer 10

My experience is that "ALTER LOGIN...DISABLE" is for SQL Standard accounts

while "ALTER LOGIN...DENY" is for Windows accounts..

one option that may work for you too- is to disable remote access (via Server Facets\Server configuration)

this will keep out ALL remote connections which may prevent the process you're working with too:unsure:

Ben Pflanzer

Sr. SQLServer DBA