Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query / Script to get the information if my domain account has access to all the SQL Servers in my...


Query / Script to get the information if my domain account has access to all the SQL Servers in my domain.

Author
Message
- Win.
- Win.
SSC Veteran
SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)

Group: General Forum Members
Points: 272 Visits: 800
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.

Cheers,
- Win.

" Have a great day "
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14942 Visits: 38937
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!

- Win.
- Win.
SSC Veteran
SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)

Group: General Forum Members
Points: 272 Visits: 800
Appreciate your fast help ..!!!


Can this be for every instance in every box for multiple instances as well.?

Can you please explain me in detail. As am not that much into .NET.

Need more help and better understanding.

Cheers,
- Win.

" Have a great day "
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14942 Visits: 38937
ok forget the do it yourself thing: download this free program:
SQLRecon

it will scan your entire network in a LOT of differnet ways, and find all installations, including multiple instances ont he same server.
it will try to connect to each of them IF you put in the credentials.
here's a pair of screenshots from me hitting my network with ~40 servers/instances:





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!

spaghettidba
spaghettidba
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5730 Visits: 13305
You could also use a small powershell script:


function connectSQL([string]$serverName, [string]$instanceName) {
   trap {
      continue
   }
   $sqlServer = $serverName
   if(!$instanceName.equals("")){
      $sqlServer = $serverName + "\" + $instanceName
   }
   $cn2 = new-object system.data.SqlClient.SQLConnection("Data Source="+ $sqlServer +";Integrated Security=SSPI;Initial Catalog=master")
   $cn2.Open()
   Write-Host $sqlServer " : " $cn2.State
}

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() | ForEach-Object {connectSQL $_.ServerName $_.InstanceName}



Remember you have to start your sqlbrowser to make it work.

--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
- Win.
- Win.
SSC Veteran
SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)

Group: General Forum Members
Points: 272 Visits: 800
Excellent Lowell

You helped me alot.... Thanks a ton for your quick and prompt response.

Cheers,
- Win.

" Have a great day "
- Win.
- Win.
SSC Veteran
SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)

Group: General Forum Members
Points: 272 Visits: 800
Gianluca Sartori,

Good One.... I will try to work with this script and post you.... Reply will be delayed, as I have to get confirmation and required permissions to do so.

Cheers,
- Win.

" Have a great day "
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search