Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Using Run As to Make Your Life Simple

By Gaby Abed,

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).

Total article views: 2558 | Views in the last 30 days: 2
 
Related Articles
ARTICLE

Query accounts, domain groups, and members who have admin membership.

This script leverages master.sys.server_principals and xp_logininfo to return accounts, domain group...

FORUM

Controlling Sql Server logins

Domain\server$ accounts and others

FORUM

SQL server accounts for development

SQL server accounts, design

FORUM

SQL Server Agent Service account

Why did I have to add our domain sql server service account manually?

FORUM

SSRS - Changing Service to use Domain Account

WMI problems when changing to domain account - can't even change back.

Tags
run as    
sp_addsrvrolemember    
sp_grantlogin    
sqlcmd    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones