July 14, 2008 at 3:00 pm
I am trying to use following command . Looks like it cannot disable NT group uses. All other users are getting disable without any problem except the following one.
ALTER LOGIN [SUTTER-CHS\SC.IT_Data_Services] disable.
Here is the error.
Msg 15151, Level 16, State 1, Line 1
Cannot alter the login 'SUTTER-CHS\SC.IT_Data_Services', because it does not exist or you do not have permission.
July 14, 2008 at 3:16 pm
Usually, NT domain users are assigned to SQL Servers and databases as parts of groups. The individual login might not be there. Check under the Users tab for the server.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 3:52 pm
Individual logins are there.
July 14, 2008 at 3:55 pm
use what kind of security?
July 14, 2008 at 4:07 pm
double check spelling of domain\username
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 14, 2008 at 10:56 pm
NT has sysadmin rites so u can disable builtin/adminstrator
July 15, 2008 at 10:42 am
Account has folllowing rights
SecurityAdmin
serveradmin
setupadmin
sysadmin
I checked the domain name and it's correct. I can disable all the logins except NT Group Logins.
July 15, 2008 at 11:34 am
What Login are you using to disable? If you can't 'trump' the NT login, then you don't have sufficient rights, and that would trigger the error message.
Can YOU login using the ID in question?
Why are you trying to disable this ID? Would disabling the password not be equally effective?
July 15, 2008 at 11:57 am
Steve.. It's a AD group account in SQL Server. The reason I am trying to disable these user account because I don't want any user to connect to the server if I am doing any processing.
DBA, who used to work here had made a process which kills user for a specific time but I don't like the idea.
Yes , disable the password is a good idea. How can I do that. ?
July 15, 2008 at 1:16 pm
It occurs to me that there's a problem with 'disabling' the password, depending on how the ID is used. IF you have administrative rights to the machine, then you put on your admin hat and change the password on the login ID - however, you have to make sure you aren't breaking anything else.
Because the ID is using Windows authentication, it's not as simple as simply changing the password within SQL Server (think back to 7.0 days, and earlier...)
The joys of not thinking things through... However, you could remove the ID, as an administrator, from having rights to SQL Server at all, or setup a 'deny read' on the database directory where the content resides ... lots of squirrelly ways of locking out, but heaven help you if you forget what you did and want to undo it.
IF you don't have admin rights, then you need to be political about this - read, not a technical discussion, no bias intended - and deal with the sys admins and your joint management to come to an understanding. You'll have to explain the whys and wherefores of what you want to accomplish and get an agreement as to how you will implement all this.
Not everyone has been burned by NOT establishing proper controls, so you may have to do some educating and preaching as well. Good hunting! (I've read lots and lots of threads on the forums about this, but can't cite anything specifically)
July 15, 2008 at 1:39 pm
From BOL under the ALTER LOGIN help:
You cannot use ALTER_LOGIN with the DISABLE argument to deny access to a Windows group. For example, ALTER_LOGIN [domain\group] DISABLE will return the following error message:
"Msg 15151, Level 16, State 1, Line 1
"Cannot alter the login 'Domain\Group', because it does not exist or you do not have permission."
This is by design.
So, I think your options are either of the following:
1) Drop the login and when done with your processing, create the login
2) Deny connect to SQL option for the group:
DENY CONNECT SQL TO [group]
GO
I would probably opt for option 2 myself.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 15, 2008 at 3:06 pm
Steve.. Thanks a lot.
I like your idea of denying connection to sql server. Here is the TSQL I am going to use. I will deny connection for all the users for specific time and then run my process and then after that Grant Connect to all the users.
DENY CONNECT SQL TO [SUTTER-CHS\SC.IT_Data_Services]
Thanks you for all your help.
July 15, 2008 at 4:21 pm
balbirsinghsodhi (7/15/2008)
Steve.. Thanks a lot.I like your idea of denying connection to sql server. Here is the TSQL I am going to use. I will deny connection for all the users for specific time and then run my process and then after that Grant Connect to all the users.
DENY CONNECT SQL TO [SUTTER-CHS\SC.IT_Data_Services]
Thanks you for all your help.
Not Steve - but you are welcome 😀
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 16, 2008 at 10:44 am
Sorry about that.
Thank you Jeffrey.
July 16, 2008 at 12:58 pm
You could try the following SQL to disable the NT group.
ALTER LOGIN [Domain\NT_Group_Name] DISABLE
GO
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply