March 15, 2012 at 5:13 am
Hi
We are an SME that currently has approx 60 instances of SQl server ( Mostly 2008 R2 ) spread over Dev/UAT and Live environments.
Access to SSMS is currently restricted to only those in IT. We have developers that perform DBA duties - but certainly not black belt DBA'a.
We now have a request / requirement to give SSMS to a member of our finance team in order to perform some set up work.
He only needs access to one of our instances ( a test server ). My question is two fold.
1. Is there any way to configure SSMS to only point to a single server ( my assumption is that there is not - unless you separate out the network )
2. My second option is to check / monitor the other servers to see which he has potential access - a login too. I know I can get the basic information from sys.syslogins - but is there a way of cycling through all of the servers on the network to check the logins. ( All servers are in the same Domain ). Im guessing that you may need to add linked servers?
Any thoughts ( and perhaps sample code ) gratefully received.
March 15, 2012 at 6:21 am
Michael Walter-477776 (3/15/2012)
Hi1. Is there any way to configure SSMS to only point to a single server ( my assumption is that there is not - unless you separate out the network )
2. My second option is to check / monitor the other servers to see which he has potential access - a login too. I know I can get the basic information from sys.syslogins - but is there a way of cycling through all of the servers on the network to check the logins. ( All servers are in the same Domain ). Im guessing that you may need to add linked servers?
1, there is no way to limit what server SSMS can connect to its all done at the authentication layer, so you would have to ensure that the user only has access to login to the server in question
2, you could create a central management server and register all your instances under this CMS so that you can execute a query across the whole environment in one go without having to change it for different linked servers
also take into account AD groups that the user might be a part of which will grant access to SQL and ensure that they cant do anything destructive to the other servers
March 16, 2012 at 7:36 am
...is there a way of cycling through all of the servers on the network to check the logins...
PowerShell is a good tool for executing queries against many servers. I'm not a PS guru, you can find plenty of scripts online, but here is a short example that you can fine tune for your environment.
$file = "C:\temp\test.txt"
if (test-path $file)
{
remove-item $file
}
foreach ($svr in get-content "C:\temp\servers.txt")
{
$dt = new-object "System.Data.DataTable"
$cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=master;Integrated Security=sspi; Connection TimeOut=30"
$cn.Open()
$sql = $cn.CreateCommand()
$sql.CommandTimeOut = 120
$sql.CommandText = "SELECT * FROM master.sys.server_principals"
try
{
$rdr = $sql.ExecuteReader()
}
catch
{
"{0} : {1}" -f $svr.toString(), $_.toString() | Out-File $file -append -width 200
continue;
}
$dt.Load($rdr)
$cn.Close()
$svr.toString() | Out-File $file -append -width 200
$dt | Format-Table | Out-File $file -append -width 200
}
March 16, 2012 at 7:41 am
Thank you. Thats a couple of great ideas that I can work with.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply