• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 672
  • Last Modified:

Modify user security on multiple sql servers.

I have a list of about 150 sql servers.  I need to create a windows script that uses this list and adds and runs as domain admin granting sysadmin to a domain group on all the servers in the list.  Looking for recommendations or a script to do this.

Maybe a batch script with for /f through the text file using osql or isql? Maybe a vbscript??

Some of these sql boxes are 2005 and some are 2000  
  • 7
  • 2
2 Solutions
You might be able to use secedit or the Security configuration manager tool to do this (although I've never done it)
HAuthor Commented:
Isnt secedit mainly for windows security?  I dont want to grant the Domain group access to the windows servers just SYSADMIN role on the sql database instance.
>granting sysadmin to a domain group

I'm not quite sure what you want to do? Can you please elaborate more on this? Do you want to add a windows user (sysadmin) to a windows domain group, or grant any access within the database? Can you create an example for one server?
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

HAuthor Commented:
I will re-explain.

I have a list of 150 sql server instances.

I have a pre-existing domain group...   domain\groupname

I want to add this pre-existing domain group to all the 150 sql servers with a scipt.  Granting this domain group the default sql server role of "SYSADMIN"

So basically I want to loop through the text file of sql server instances maybe using a tool and create the security login and sysadmin role...

This way I wont have to connect to each sql instance and manually grant the access.

HAuthor Commented:
I will be running this script with an account that has full access to all the sql servers...IE a domain admin....
HAuthor Commented:
Im thinking something like this?  Or maybe theres and easier way?

FOR /F %%G IN (sqlserverlist.txt) DO osql -T   -d %%G  -t 4 -Q"EXEC sp_addlogin 'MyAdmin','Qxy43TMki', 'MyDatabaseName'"

FOR /F %%G IN (sqlserverlist.txt) DO osql -T   -d %%G   -t 4 -Q"EXEC sp_grantdbaccess 'MyAdmin',  'MyAdmin'"

FOR /F %%G IN (sqlserverlist.txt) DO osql -T   -d %%G   -t 4 -Q"EXEC sp_addrolemember N'db_owner', 'MyAdmin'"

HAuthor Commented:
Maybe this is a little closer...

FOR /F %%G IN (sqlserverlist.txt) DO osql -E   -S %%G  -t 4 -Q"EXEC sp_addlogin [DOMAIN\Group Name]"


FOR /F %%G IN (sqlserverlist.txt) DO osql -E   -S %%G  -t 4 -Q"CREATE LOGIN [DOMAIN\Group Name] FROM WINDOWS WITH DEFAULT_DATABASE=[master]"

Add sys admin

FOR /F %%G IN (sqlserverlist.txt) DO osql -E   -S %%G   -t 4 -Q"EXEC master..sp_addsrvrolemember @loginame = N'DOMAIN\Group Name', @rolename = N'sysadmin'"

I would do it with sqlcmd:

=> batch script: run-sql.bat

sqlcmd -S myServer1\instanceName -i C:\myScript.sql
sqlcmd -S myServer2\instanceName -i C:\myScript.sql
sqlcmd -S myServer3\instanceName -i C:\myScript.sql
sqlcmd -S myServer4\instanceName -i C:\myScript.sql
sqlcmd -S myServer5\instanceName -i C:\myScript.sql

=> myscript.sql

EXEC sp_addsrvrolemember 'Domain\Group', 'sysadmin';

==> sqlcmd util:


HAuthor Commented:
Okay so something like this should work...Let me run some tests...

FOR /F %%G IN (SQLLIST.TXT) DO sqlcmd -S %%G -q "EXEC master.dbo.sp_grantlogin @loginame = N'domain\groupname'"
FOR /F %%G IN (SQLLIST.TXT) DO sqlcmd -S %%G -q "EXEC master.dbo.sp_grantlogin @loginame = N'domain\groupname'"
FOR /F %%G IN (SQLLIST.TXT) DO sqlcmd -S %%G -q "EXEC master.dbo.sp_defaultdb @loginame = N'domain\groupname', @defdb = N'master'"
FOR /F %%G IN (SQLLIST.TXT) DO sqlcmd -S %%G -q "EXEC master.dbo.sp_defaultlanguage @loginame = N'domain\groupname'"
FOR /F %%G IN (SQLLIST.TXT) DO sqlcmd -S %%G -q "EXEC master..sp_addsrvrolemember @loginame = N'domain\groupname', @rolename = N'sysadmin'"

Open in new window

HAuthor Commented:

I ended up using this seemed to work great.

Thanks for your help
FOR /F %%G IN (SQLLIST.TXT) DO osql -E -S %%G  -Q"EXEC master.dbo.sp_grantlogin @loginame = N'DOMAAIN\GROUP'" 
FOR /F %%G IN (SQLLIST.TXT) DO osql -E -S %%G  -Q"EXEC master.dbo.sp_defaultdb @loginame = N'DOMAAIN\GROUP', @defdb = N'master'"
FOR /F %%G IN (SQLLIST.TXT) DO osql -E -S %%G  -Q"EXEC master.dbo.sp_defaultlanguage @loginame = N'DOMAAIN\GROUP'"
FOR /F %%G IN (SQLLIST.TXT) DO osql -E -S %%G  -Q"EXEC master..sp_addsrvrolemember @loginame = N'DOMAAIN\GROUP', @rolename = N'sysadmin'"

Open in new window


Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now