what if yo udo not know the servers to whic hlogins belong......

Request :
I was give a text file with a list of logins to be removed from SQL Servers as those employees were no more existing .I asked if they know the server names where the logins exist .The answer was an expected NO ....made my task more dificult .But I had to do it . I came out with a script that can be used to find the instance name where the logins exist .I have not created the script to remove those logins as its pretty simple ,but have explained in brief what can be done .So my juniors will do it :) ...

Requirements :
-> SQL Server 2008 Express edition [Central management Servers]
We will be using it to execute queries on a group of servers and getting the consolidated result .

-> .regsrvr file
We will modify this file and add steps to add more servers instead of registering instances manually one by one .

-> login_info view
This view will keep the information about all the logins having users and schemas associated with them .If there is a login which has no user associated with it , it will not show up in this view.
-> Instances table
This will keep the list of all the 2005 instances in a domain .
-> Del_logins table
This will keep the list of all the logins that have to be searched for deletion.

-> Instances.txt
This will keep the list of the instances that will be BCPed in to instances table

-> logins.txt
This will have the list of all the logins that have to be deleted.This wil be BCPed to del_login table
-> BCP
We will be using BCP in ,to import values from instances.txt and logins.txt.

-> :connect
We wil be using :connect to connect to instances and and doing BCP in.

-> WMI
We will use WMI to copy files to different servers .



Important :
Before doing anything you need to register the servers in the CMS .
register a couple of servers and then export the .regsrvr file somewhere.
open that file in SQl Server QA .You can easily see the pattern of instance registration .Just copy the same as many time as there are the instances and change the instance names .Import it back and you have the instances under the server group name .



--Step 1: create login_info view on all servers using CMS.

use master
go
create view login_info as
select d.name as [user_name],d.type_desc as [user_type],s.name as[login_name],s.type_desc as [login_type],d.default_schema_name as [schema_name]
from sys.database_principals d right outer join sys.server_principals s
on d.sid =s.sid
where s.type not in ('R')


--Step 2:Copy the logins.txt that the client gave to all the 2005 servers in the common drive .
--this is a VBS file which will copy test.doc to different servers listed in computers.txt.

Const ForReading = 1
Const OverwriteExisting = TRUE

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Scripts\Computers.txt")

Do Until objFile.AtEndOfStream
strComputer = objFile.ReadLine
strRemoteFile = "\\" & strComputer & "\C$\Scripts\LOGINS.TXT"
objFSO.CopyFile "C:\Scripts\Test.doc", strRemoteFile, OverwriteExisting
Loop




-- step 3: Create a text file instances.txt with 2005 instance names on any one server with SQL Server installed .
-- you can copy it from the inventory or try importing it with xp_cmdshell using OSQL -L and inporting it to a table
/*
sp_configure 'show advanced options',1
reconfigure with override
sp_configure 'xp_cmdshell',1
reconfigure with override

create table test (ins varchar(30))
go

insert into test exec xp_cmdshell 'osql -L'

Then delete few unnecessary rows or you need to modify the output by >> Query >> Query options

*/

-- Step 4: Create the instances table in the 2005 instance server you copied the file above.

use master
go
Create table instances (iname sysname)



-- Step 5: BCP in the instance names from the instances.txt files to instances table .
xp_cmdshell 'bcp master.dbo.instances in c:\temp\instances.txt -T -c -S

-- sTEP 6: create del_logins in all the instances using CMS
create table master.dbo.del_logins (lname varchar(50))

-- Step 7: BCP in values from the text file to the del_login table (run this command on the same server where you have instances table .Copy the output to the CMS and execute)

Create table #inst (iname sysname,flag int identity (1,1) )
Insert into #inst select iname from instances
Declare @@flag1 int
Select @@flag1 =max (flag) from #inst
While @@flag1 >0
Begin
Declare @string nvarchar (100)
DECLARE @string1 nvarchar(50)
Declare @iname sysname
Select @iname =iname from #inst where flag=@@flag1
Set @string ='xp_cmdshell '+ '''bcp master.dbo.del_logins in c:\temp\logins.txt -T -c -S' +@iname+''''
set @string1= ':connect' +@iname
print @string1
print @string
print 'GO'
Set @@flag1 =@@flag1 -1
End
Drop table #inst

-- Step 8: We need to run an intersect query in CMS

select login_name from master.dbo.login_info
intersect
select lname from master.dbo.del_logins

Result :
----------

This output will give you the logins that exist on the different servers .And then you can drop those logins one by one .

Other way to drop the logins is to create a table with servername , login name and a flag default to an identity starting with 1.

create the SP around the intesect output and then insert it to this table .
run it in the while loop and drop the logins .

You can also create the scrip to change the schema to DBO before dropping the users and logins.

mail me for any clarification at : hi_abhay78@yahoo.co.in

Happy learning..
Abhay