Using Run As to Make Your Life Simple

kurianchirackel, 2013-06-06 (first published: 2009-03-16)

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

How to use RunAs command for SSMS if option does not exist

by Additional Articles

MSSQLTips.com

Run As

In order to use your normal Windows login and your admin login to connect to SQL Server using SSMS you need to use the "Run as" feature. What do you do in the case of Windows 7 or Windows Vista where you can’t find the Run As Different User option?

2012-03-08

2,888 reads

Treating SQL Server as an Object with PowerShell

by Additional Articles

SimpleTalk

PowerShell

It’s easy to use invoke-sqlcmd to send T-SQL statements to SQL Server from PowerShell. There is, however, a rich PowerShell library, sqlps, that can be used instead. In this article, Greg Moore shows how to use sqlps to treat a SQL Server instance as an object.

2018-12-10

1,257 reads

SQL Server SQLCMD Basics

by Additional Articles

SimpleTalk

Sqlcmd makes many SQL Server tasks, such as automating test runs and maintenance tasks, easier and quicker. The sqlcmd command-line utility is valuable to any database developer or DBA as the prime means of executing batches of SQL Statements to SQL servers, and saving results to file. Rob Sheldon gives you the basic facts about this great utility.

2013-11-08

6,993 reads