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  
LVL 8
HDatabase AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
You might be able to use secedit or the Security configuration manager tool to do this (although I've never done it)
0
HDatabase AdministratorAuthor 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.
0
kuknoCommented:
>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?
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

HDatabase AdministratorAuthor 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.

0
HDatabase AdministratorAuthor Commented:
I will be running this script with an account that has full access to all the sql servers...IE a domain admin....
0
HDatabase AdministratorAuthor 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'"


0
HDatabase AdministratorAuthor Commented:
Maybe this is a little closer...

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

OR

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'"


0
kuknoCommented:
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
etc.

=> myscript.sql

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


==> sqlcmd util:

http://msdn.microsoft.com/en-us/library/ms162773(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms170207(SQL.90).aspx

Regards
Kurt
0
HDatabase AdministratorAuthor 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

0
HDatabase AdministratorAuthor Commented:
Hi

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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.