In short, I'm trying to get the servername, databasename, userID and role from every SQL Server 2000, and load this information into one table on one server, that I'll reference as the MAINSVR.

Currently this is working with the script below, but I have a DTS package & job, along with a link to MAINSVR on each individual server.

My QUESTION: how can I get this to run from MAINSVR with one job and one DTS package? I want to link all servers to the MAINSVR and run this script once, for easier management should there be a change in the script, etc.

I appreciate your time and ideas!
Thank you,
ChelleV
------------------------

SET NOCOUNT ON

DECLARE crs_Databases CURSOR FOR
SELECT name
FROM master..sysdatabases
WHERE name not in('master','msdb','model','pubs','northwind','tem pdb')
ORDER BY dbid

DECLARE @str_Database varchar(40)
CREATE TABLE #tbl_ARM
(
tmb_ServerName varchar(25) NULL
,tmb_DBName varchar(40) NULL
,tmb_UserID varchar(25) NULL
,tmb_GroupID varchar(25) NULL
)


OPEN crs_Databases

FETCH NEXT FROM crs_Databases INTO @str_Database

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO #tbl_ARM
--GET ALL ROLES FOR A USER
EXEC('SELECT
CONVERT(varchar(25),@@SERVERNAME) Servername
,' + '''' + @str_Database + '''' + '
,CONVERT(varchar(25),su1.name) UserID
,CONVERT(varchar(25),su2.name) RoleID
FROM [' + @str_Database + '].dbo.sysmembers
INNER JOIN [' + @str_Database + '].dbo.sysusers su1
ON memberuid = su1.uid
INNER JOIN [' + @str_Database + '].dbo.sysusers su2
on groupuid = su2.uid')

FETCH NEXT FROM crs_Databases INTO @str_Database
END

CLOSE crs_Databases
DEALLOCATE crs_Databases

--GET ALL MEMBERS OF THE SYSADMIN ROLE
INSERT INTO #tbl_ARM
SELECT
CONVERT(varchar(25),@@SERVERNAME) Servername
,sd.name DBName
,CASE
WHEN sl.name IS NOT NULL THEN CONVERT(varchar(25),sl.name)
ELSE CONVERT(VARCHAR(25), sl.loginname)
END UserID
,'db_owner'
FROM master.dbo.syslogins sl
CROSS JOIN master.dbo.sysdatabases sd
WHERE sysadmin = 1

insert into DSMNTAPPS02.DBManagement.dbo.RoleMembers
( mbr_Servername,
mbr_DBname,
mbr_MemberID,
mbr_GroupID,
row_created_date
)

select tmb_ServerName, tmb_DBName, tmb_UserID, tmb_GroupID, getdate()
from #tbl_ARM
DROP TABLE #tbl_ARM

You know what? I wouldn't use SQL Server per se. I would use a simple loop
in SQLDMO to do the work for me. It's an intuitive object model and quite
easy to implement. i would as I loop through the objects use ADO to do my
inserts.



--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"ChelleV" <anonymous (AT) discussions (DOT) microsoft.com> wrote

server, that I'll reference as the MAINSVR.
once, for easier management should there be a change in the script, etc.

Allan - thank you for your response. I have never used SQLDMO/ADO. Can you just give me some simple instructions on how to start looking into this or where to go to read about it?

Thanks!

I wrote a book on it but do not expect you to buy it so. In my Enterprise
Manager I have a list of servers. if they ain't in here then they do not
exist so when i want to perform maintenance on them I loop through them.
Here is a brief rundown

EM is broken into

ServerGroups
RegisteredServers (I then use the SQLServer object pasing in the details
of the registered server)
Database
Database Collections

Here is some sample code that will loop through

Register the SQLDMO library in VB first.

Private oServer As SQLDMO.SQLServer
Private orServer As RegisteredServer
Private oDB As Database
Private oTable As Table

Private Sub Command1_Click()

For Each orServer In SQLDMO.ServerGroups(1).RegisteredServers '
registered servers

'do something ' here you can log the name of the server

Set oServer = New SQLServer 'Log in

oServer.Name = orServer.Name
oServer.LoginSecure = True

oServer.Connect

For Each oDB In oServer.Databases ' databases collection of the
server

'do something

For Each oTable In oDB.Tables 'tables collection of the server

'do something

Next oDB

oServer.DisConnect ' diconnect from the server ready for next

Next

End Sub


the Database Object has a DatabaseRoles collection.



Good reading resources are BOL and Gert (www.sqldev.net)....
and my book


--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"ChelleV" <anonymous (AT) discussions (DOT) microsoft.com> wrote

or where to go to read about it?

If you go to www.sqldts.com there is a revolving book icon on the homepage.
My book will come up there. It is on SQLDMO. Yes it should be available on
Amazon and that is where you will be taken to when you click on our icon.

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"ChelleV" <anonymous (AT) discussions (DOT) microsoft.com> wrote

your time and ideas.