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?
 
HConnect With a Mentor Database 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
 
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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
 
kuknoConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.