SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Block windows groups


Block windows groups

Author
Message
Alren
Alren
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 377
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!Sad

Has anyone ideas how to do this ?
homebrew01
homebrew01
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22096 Visits: 9222
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
Alren
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 377
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
homebrew01
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22096 Visits: 9222
You could disable the login, or remove permission to the specific database(s) so they can still access other databases.



Alren
Alren
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 377
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...Sad

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! Cool
homebrew01
homebrew01
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22096 Visits: 9222
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 ?



ALZDBA
ALZDBA
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52953 Visits: 9116
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


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


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


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Alren
Alren
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 377
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 Cool
Alren
Alren
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 377
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 Cool

ALZDBA
ALZDBA
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52953 Visits: 9116
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 Cool
[/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


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


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


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search