Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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: 2580 | 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