October 11, 2008 at 3:31 pm
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 ?
October 13, 2008 at 9:30 am
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 ?
October 13, 2008 at 9:54 am
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 ?
October 13, 2008 at 10:16 am
You could disable the login, or remove permission to the specific database(s) so they can still access other databases.
October 13, 2008 at 11:11 am
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! 😎
October 13, 2008 at 11:31 am
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 ?
October 13, 2008 at 12:28 pm
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
October 13, 2008 at 1:49 pm
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 😎
October 13, 2008 at 1:55 pm
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 😎
October 14, 2008 at 12:09 am
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
September 21, 2010 at 6:34 am
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply