Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query / Script to get the information if my domain account has access to all the SQL Servers in my domain. Expand / Collapse
Author
Message
Posted Thursday, July 8, 2010 5:59 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 29, 2012 5:22 AM
Points: 260, 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 "
Post #949218
Posted Thursday, July 8, 2010 6:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:03 AM
Points: 12,905, Visits: 32,167
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #949229
Posted Thursday, July 8, 2010 7:00 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 29, 2012 5:22 AM
Points: 260, 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 "
Post #949259
Posted Thursday, July 8, 2010 7:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:03 AM
Points: 12,905, Visits: 32,167
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #949268
Posted Thursday, July 8, 2010 9:05 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:28 PM
Points: 4,421, Visits: 10,739
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
Post #949369
Posted Friday, July 9, 2010 2:45 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 29, 2012 5:22 AM
Points: 260, Visits: 800
Excellent Lowell

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





Cheers,
- Win.

" Have a great day "
Post #949784
Posted Friday, July 9, 2010 2:47 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 29, 2012 5:22 AM
Points: 260, 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 "
Post #949787
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse