Using Run As to Make Your Life Simple

,

Recently, we decided that we had to update 200+ store servers by adding a new SQL Admin group to each of those servers. As I do not relish the idea of doing mass updates to servers, especially to those on different domains, I decided to look into creating batch files and running the batch file using Run As for the different domain. I work in a domain, let's call it MYDOMAIN and the stores are the STORES domain. I need to generate a whole bunch of 'sp_grandlogin' and 'sp_addsrvrolemember' scripts.

The first thing I did was create a SQL script to run against a list of servers I have and generate queries in batches of 10. Once they're generated I copy them all to an update_stores.bat file.

The second step, the crucial one, is to open a command prompt by right-clicking on it, selecting Run As, and registering under my own personal account in the STORES domain (i.e. move from MYDOMAIN\Gaby_Abed to STORES\Gaby_Abed, which is a member of STORES\SQLAdmins). Once the command prompt opens, I then run the batch file, pausing every 10 stores to make sure I catch any errors (timeouts, already existing logons, etc.) The key with the registration is the initial insert is done with the 'sa' account (so yes, the sa password is exposed temporarily...delete your script when done), and the followup is logging on with trusted autentication (-E against my STORES account).

set nocount on
declare @servername varchar(10)

select * into #store_servers from store_servers

declare @batchnum int, @iter int

set @batchnum = 0; set @iter = 0

while (select count(*) from #store_servers) > 0
begin
 if @iter = 0
 begin
 set @batchnum = @batchnum + 1
 print 'echo BATCH ' + cast(@batchnum as varchar) + ''
 print 'pause'
 end
 select top 1 @servername = servername from #store_servers
 print 'sqlcmd -S ' + @servername + ' -U sa -P XXXXXX -l 5 -Q "exec sp_grantlogin ''STORES\SQLAdmins''; exec sp_addsrvrolemember ''STORES\SQLAdmins'', ''sysadmin'';"'
 print 'sqlcmd -S ' + @servername + ' -E -l 5 -Q "print ''' + @servername + '''"'
 print ''
 delete from #store_servers where servername = @servername
 set @iter = @iter + 1
 if (@iter % 10) = 0  -- My batches of 10 delimiter
   set @iter = 0
end

drop table #store_servers

Rate

4 (2)

Share

Share

Rate

4 (2)