ALTER LOGIN

Question

Post reply

ALTER LOGIN

balbirsinghsodhi

SSCrazy Eights

Points: 9210
More actions
July 7, 2008 at 6:26 pm

#92002

I am running following command and getting Incorrect syntax near '-'.
ALTER LOGIN SUTTER-CHS\SC.IT_Data_Services ENABLE
I tried putting login in the code but does not work.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736297 More actions · Answer 1

I think the hyphen is causing issues. Try enclosing the name in []

gbd77rc Ten Centuries Points: 1031 More actions · Answer 2

Hi

Try

ALTER LOGIN [SUTTER-CHS\SC.IT_Data_Service] ENABLE

It normally works with [] around the domain\username.

Regards

Richard...

http://www.linkedin.com/in/gbd77rc

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 3

I tried using [ ] but getting following error.

Msg 15151, Level 16, State 1, Line 1

Cannot alter the login 'SUTTER-CHS\SC.IT_Data_&_Web_Services', because it does not exist or you do not have permission.

gbd77rc Ten Centuries Points: 1031 More actions · Answer 4

Hi

If SUTTER-CHS\SC.IT_Data_Services is a domain/local group then you cannot change the login, it is by design.

See http://msdn.microsoft.com/en-us/library/ms189828.aspx

See http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3544179&SiteID=17

If it is a domain/local user then you can do the following :-

You need to make sure your account has the correct permission

GRANT ALTER ANY LOGIN TO [Your Account you are using to change the LOGIN]

OR more secure permission

GRANT ALTER ON LOGIN::[SUTTER-CHS\SC.IT_Data_Services] TO [Your Account you are using to change the LOGIN]

Then you can run the ALTER LOGIN command

ALTER LOGIN [SUTTER-CHS\SC.IT_Data_Services] ENABLE

I hope this helps.

Richard...

http://www.linkedin.com/in/gbd77rc

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 5

I tried but no luck. Looks like AD group login has issue. Actually, what I was tyring to do is to disable some logins for particular time so that process should not interupt and than enable the logins.

Is there any way I can stop some logins to connect to the server because of some process.

I don't want to use kill command or Start SQL Server in a single user mode.

Eric Willemstein SSChasing Mays Points: 610 More actions · Answer 6

Ok I know adding [] works, however debugging some code I see this:

set @execsql = 'alter login ' + @logname + ' disable'

The code returns invalid sytanx error at '-' as there is a '-' in the user name. I know I should know the way to fix this but am drawing a blank. suggestions?

Eric