April 10, 2008 at 9:23 am
Hi all,
I have inherited an OLD process for cleaning up SQL logins at the time of employee termination.
Current Process
I was given a .cmd file that uses ISQL command line to run a query against all servers with some specific criteria...as you can imagine it does one command after another and it takes a long time...longer than I want it to...
So I would go into the RUN line command...give the UNC to the .cmd file...followed by a space and the sql login ID
Example
\\FS10001\utilities\remove_employee.cmd 123456
Which runs the following out of a .cmd over and over and over...
isql /U xxx /P xxx /S SQLServer /d master /Q "set nocount on select name, dbname from syslogins where name = '%1'"
Then on top of it you have to scan through the results of each query to find a login that might "possibly" match the criteria of the login ID and investigate...
Very mind numbing and annoying.
Plan
1. Pick a base server
2. Add linked servers for each of the servers I want to query
For this step I haven't figured out if I want to use a special account on each remote server that only has db_datareader rights on the master db or just use an SA to SA type linked server to each server...
3. build a script that will loop through a list of servers to query each of them for login cross reference checks and insert the results if any into a temp table
4. pull any login information out of the temp table for easy reading
If that sounds good...I was going to take it a step further to attempt to automatically drop the login but I think that would be a little more complex at this time
I would have to figure out first what rights they have in each database
Build a script on the fly that drops the rights for each role/group...and then drop the login...
vs.
The manual route I am doing right now of going to Enterprise Damager and browsing each server, right-clicking and deleting the account...(I know...so noobish :D)
I am just kind of coming up with this in my head as I write this so if anyone is bored or has some recommendations I am all ears.
Thanks in advance!
Lee
April 15, 2008 at 8:56 am
I would have thought I would have gotten at least One reply 😀
Isn't anyone bored enough to give me there two cents!!!
April 15, 2008 at 9:59 am
We picked up Red Gate's SQL MultiScript tool for the data team (actually, we got the whole toolbelt, but this was a big reason for it), and cleaning out logins and users across all the servers and databases is one of the handy uses for it.
I've got a distribution set of all production databases, and one with one connection per server. I fire commands similar to the ones in your example, except all our DBs are SQL 2005, so I use sys.database_principals and sys.server_principals.
There's a couple different tools out there that provide this functionality, including RedGate's MultiScript, it's included with SQLFarms' Combine tools, and the SQL 2008 SSMS has it as well.
I normally push for rolling your own solution to a lot of problems, but this MultiScript tool rocks for this sort of multi-server admin thing.
-Eddie
Eddie Wuerch
MCM: SQL
April 15, 2008 at 10:46 am
For as large of installation that we have you would "THINK" that would be advisable or considered but spending money on things like that "utilities" isn't high on the list as like most places...
Thus I have been given the option to go the manual route or come up with something on my own...which is why I am asking...
To be honest I have never used any sort of utilities because as crazy as it sounds it is almost frowned upon in my current setting...
July 12, 2008 at 4:33 am
Thus I have been given the option to go the manual route or come up with something on my own...which is why I am asking...
The SQL server using the data base stored, now the future SQL is SQL2005 version’s it develop will be the future.
*************************************
shanmarsh
July 12, 2008 at 4:35 am
Thus I have been given the option to go the manual route or come up with something on my own...which is why I am asking...
The SQL server using the data base stored, now the future SQL is SQL2005 version’s it develop will be the future.
*************************************
shanmarsh
July 12, 2008 at 5:15 am
Add linked servers for each of the servers I want to query
The problem with linked servers is that if any of the SQL Servers are not accessable, this will cause a batch termination.
Alternatively, on a central SQL Server
1. Have a database table that has colums for SQL Server name, login and whatever other columns are needed.
2. Create a DTS Package that has a global variable for the remote SQL Server name, a transform data task from the remote SQL Server to the central table.
3. Execute the DTS package passing the name of the remote SQL Server.
SQL = Scarcely Qualifies as a Language
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply