Query / Script to get the information if my domain account has access to all the SQL Servers in my domain.
-
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 "
-
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! -
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 "
-
ok forget the do it yourself thing: download this free program:
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! -
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
-
Excellent Lowell
You helped me alot.... Thanks a ton for your quick and prompt response.
Cheers,
- Win." Have a great day "
-
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 "
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply