Alter logins accross many servers without cms

is it possible to do an 'ALTER LOGIN [loginname] DISABLE' using either openrowsource or opendatasource?

And if so how can i do this?

I have a list of servers that i need to change all users with a specific name to disabled.
Ward MinsondeveloperAsked:
Who is Participating?
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Don't think it is of much use, as you have to provide connection info literally (no var allowed), but here it is:
exec opendatasource('SQLNCLI', 'Server=TheServer\TheInstance; Integrated Security=SSPI ').master.dbo.sp_executesql N'alter login JamesBond disable'

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You can use EXEC with a server link to execute remote.
0
 
Ward MinsondeveloperAuthor Commented:
Qlemo
could you show me an example of what you are suggesting?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
declare @name = nvarchar(100)
set @name = 'whoever'
exec ('ALTER LOGIN ' + @name + ' DISABLE') at LinkedServerName

Open in new window

You can code the user name literally into the command, of course, if you don't need it to be variable.
0
 
Ward MinsondeveloperAuthor Commented:
is there any other way with out creating a linked server?
0
 
Ward MinsondeveloperAuthor Commented:
I am planning on creating the connection string dynamically and then run the alter on the  server that needs the change.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Then you'll have to create a dynamic SQL string containing the complete command starting with opendatasource, and execute that with EXEC or sp_executesql. As you should know, neither opendatasource nor openrowset allow dynamic connection strings.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can use OPENDATASOURCE. The complete guide to this command is here in MSDN.
0
 
Ward MinsondeveloperAuthor Commented:
this worked with my original script very well thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.