Post reply

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

  • 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

  • May 26, 2010 at 7:49 am

    #1172723

    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!

  • May 26, 2010 at 7:58 am

    #1172737

    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

  • May 26, 2010 at 8:07 am

    #1172746

    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!

  • May 26, 2010 at 8:12 am

    #1172754

    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.

  • May 26, 2010 at 8:58 am

    #1172795

    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?

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

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