Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Block windows groups Expand / Collapse
Author
Message
Posted Saturday, October 11, 2008 3:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:18 AM
Points: 14, Visits: 270
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 ?
Post #584467
Posted Monday, October 13, 2008 9:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:04 AM
Points: 2,795, Visits: 8,298
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 ?


Post #584980
Posted Monday, October 13, 2008 9:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:18 AM
Points: 14, Visits: 270
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 ?
Post #584996
Posted Monday, October 13, 2008 10:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:04 AM
Points: 2,795, Visits: 8,298
You could disable the login, or remove permission to the specific database(s) so they can still access other databases.


Post #585011
Posted Monday, October 13, 2008 11:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:18 AM
Points: 14, Visits: 270
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!
Post #585037
Posted Monday, October 13, 2008 11:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:04 AM
Points: 2,795, Visits: 8,298

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 ?



Post #585042
Posted Monday, October 13, 2008 12:28 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:27 AM
Points: 6,997, Visits: 8,411
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #585055
Posted Monday, October 13, 2008 1:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:18 AM
Points: 14, Visits: 270
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
Post #585085
Posted Monday, October 13, 2008 1:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:18 AM
Points: 14, Visits: 270
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
Post #585086
Posted Tuesday, October 14, 2008 12:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:27 AM
Points: 6,997, Visits: 8,411
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #585213
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse