Home Forums SQL Server 2005 Administering Query / Script to get the information if my domain account has access to all the SQL Servers in my domain. RE: Query / Script to get the information if my domain account has access to all the SQL Servers in my domain.

  • July 8, 2010 at 6:17 am

    #1190184

    I would do this outside of SQL with either a sqlcmd , cmd script or an application. personally doing it in vb.NET is easiest for me, your milage may vary.

    the basic steps would be to get a list of all the servers....OSQL -L > C:\ServerList.txt from a cmd window would give you a text file of all the servers that are broadcasting their info.

    you can also get the list of servers in a .NET application with the Imports

    Microsoft.SqlServer.Management.Smo reference; Smo.SmoApplication.EnumAvailableSqlServers(False) returns a datatable of all the available servers, along with a lot more info(isLocal or not, more...

    from there, then just create a loop that uses the exact same connection string over and over again, using trusted connection and substituting only the server name.

    log which ones are failures and which connect, and you have your results.

    - Win. (7/8/2010)

    Hi All,

    Can anyone suggest me on the below query:

    Am working as a Prod DB Support Engr. We have a requirement like to check if the account XYZ\SQL_DBA_XYZ has access on all the SQL Machines in my domain XYZ at a time.

    As we are supporting 1400+ servers and its a big head ache to login into and check each server for access and have multiple instances in each box.

    So was worried - If we can have a script or a query to get the information that these many servers have access to the account and with pwd provided and it should be enabled.

    Sorry all, if I have asked a strange and silly question. But for me its a big deal, as it may take days & months on checking this. One more is if the login is not avail, I have to create and assign roles.

    Please post me a reply and suggestions and let me know how can it be possible to have.

    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!