A script to create login to a list of Servers and instances

Question

A script to create login to a list of Servers and instances

bugop

Mr or Mrs. 500

Points: 559
More actions
May 26, 2010 at 7:44 am

#141072

Could somebody point me to the right place, or a script.
I have a list of sql servers, 2000, 2005 and 2008 with as many number of instances. I would like to create a common login with sysadmin rights to these servers.
Any script or suggestion will be highly appreciated.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

well you gotta be sysadmin to add sysadmins...so can we assume you can login as 'sa' on every machine?

the easiest way would be to add your windows login to each machine...but that pre-supposed you already have an administrative login so you can add to each machine....

so if i had, say 30 servers on my network, i'd have to login to each machine and run either of the following commands:, depending on if i want my wndows login or a new "superman" sql login

USE [master]

GO

CREATE LOGIN [DISNEY\lizaguirre] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

GO

EXEC master..sp_addsrvrolemember @loginame = N'YourDomain\lowell', @rolename = N'sysadmin'

GO

--or

CREATE LOGIN [superman] WITH PASSWORD=N'NotARealPassword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

EXEC master..sp_addsrvrolemember @loginame = N'superman', @rolename = N'sysadmin'

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bugop Mr or Mrs. 500 Points: 559 More actions · Answer 2

But i want a script that can read from a list of servers..say i have these servers listed in a text file, and adds the required login, instead of doing this to every machine manually

Lowell SSC Guru Points: 323518 More actions · Answer 3

but having credentials that are valid is the precursor to doing anything to each server.

so what if i know databaseserver.google.com exists, can i loginto it with a known username? can't add anyone as a sysadmin unless i already have the ability to login, right?

does your file have servername, username, password, or just a servername?

ok, reading from a list of servers, whether in a table or in a file is not the issue, it is logging into that server that is the issue;

can you assume your own domain login, with trusted connection will let you login to the server?

here's a blazing fast way to get data from a file into a table:

CREATE TABLE AllMyServers(ServerName VARCHAR (200))

BULK INSERT AllMyServers FROM 'c:\Serverlist.txt'

WITH (

DATAFILETYPE = 'char',

FIELDTERMINATOR = ',',

ROWTERMINATOR = '',

FIRSTROW = 1

)

select * from AllMyServers

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bugop Mr or Mrs. 500 Points: 559 More actions · Answer 4

Ok, i understand your point. These servers have different sysadmin accounts and these are provided in a connection string.

So this script would be able to login to these servers via the connection string and then create another user with sysadmin right. The idea is to have a common login to all these servers. Currently each server has a different admin account.

adrian.saunders SSCommitted Points: 1517 More actions · Answer 5

Could you tell us more about the server setup? Are they all in an AD or workgroup? Are they even on the same network? What rights do you have over the server rather than SQL?