50 and more servers, 200 and more datyabases, HOW TO list USERS ?

5 Jun 2006 3:41 PM

Eric DonBanziani

Hi everybody,

I have more than 50 servers. Each server have 1 to 20 or more databases. All
databases are SQL SERVER 2000 SP3 / SP4.
Does anyone knows a script or a tool to list each DATABASE users ?

It's borrying to use Enterprise Manager, connect each server, each database,
and export user list...

Thanks for your help....

5 Jun 2006 3:49 PM

Keith Kratochvil

Have you used Query Analyzer? You may find that it is much more simple to
use (not to mention, faster) for many tasks.

--
Keith Kratochvil

Show quote

"Eric DonBanziani" <Eric DonBanzi***@discussions.microsoft.com> wrote in
message news:4FAE9B0D-07F3-458C-89D5-8183CF4ECE26@microsoft.com...
> Hi everybody,
>
> I have more than 50 servers. Each server have 1 to 20 or more databases.
> All
> databases are SQL SERVER 2000 SP3 / SP4.
> Does anyone knows a script or a tool to list each DATABASE users ?
>
> It's borrying to use Enterprise Manager, connect each server, each
> database,
> and export user list...
>
> Thanks for your help....

5 Jun 2006 4:03 PM

Eric DonBanziani

If i use Query Analyzer, should i open each DATABASE manually ?

I haven't find how to make this magic script:

-Connect each server
-connect each database
-extract each database user

Thanks.

Show quote

"Keith Kratochvil" wrote:

> Have you used Query Analyzer? You may find that it is much more simple to
> use (not to mention, faster) for many tasks.
>
> --
> Keith Kratochvil
>
>
> "Eric DonBanziani" <Eric DonBanzi***@discussions.microsoft.com> wrote in
> message news:4FAE9B0D-07F3-458C-89D5-8183CF4ECE26@microsoft.com...
> > Hi everybody,
> >
> > I have more than 50 servers. Each server have 1 to 20 or more databases.
> > All
> > databases are SQL SERVER 2000 SP3 / SP4.
> > Does anyone knows a script or a tool to list each DATABASE users ?
> >
> > It's borrying to use Enterprise Manager, connect each server, each
> > database,
> > and export user list...
> >
> > Thanks for your help....
>
>
>

5 Jun 2006 5:36 PM

Helmut Woess

Am Mon, 5 Jun 2006 09:03:02 -0700 schrieb Eric DonBanziani:

Show quote

> If i use Query Analyzer, should i open each DATABASE manually ?
>
> I haven't find how to make this magic script:
>
> -Connect each server
> -connect each database
> -extract each database user
>
> Thanks.
>
>
> "Keith Kratochvil" wrote:
>
>> Have you used Query Analyzer? You may find that it is much more simple to
>> use (not to mention, faster) for many tasks.
>>
>> --
>> Keith Kratochvil
>>
>>
>> "Eric DonBanziani" <Eric DonBanzi***@discussions.microsoft.com> wrote in
>> message news:4FAE9B0D-07F3-458C-89D5-8183CF4ECE26@microsoft.com...
>>> Hi everybody,
>>>
>>> I have more than 50 servers. Each server have 1 to 20 or more databases.
>>> All
>>> databases are SQL SERVER 2000 SP3 / SP4.
>>> Does anyone knows a script or a tool to list each DATABASE users ?
>>>
>>> It's borrying to use Enterprise Manager, connect each server, each
>>> database,
>>> and export user list...
>>>
>>> Thanks for your help....
>>
>>
>>

For the servers i dont know how to do this automatic with a sql statement,
but for the databases on a server there are so magic (and in BOL not
documented) functions like sp_msforeachdb ... :
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm

bye, Helmut

5 Jun 2006 9:55 PM

Erland Sommarskog

Eric DonBanziani (Eric DonBanzi***@discussions.microsoft.com) writes:

> I have more than 50 servers. Each server have 1 to 20 or more databases.
> All databases are SQL SERVER 2000 SP3 / SP4. Does anyone knows a script
> or a tool to list each DATABASE users ?
>
> It's borrying to use Enterprise Manager, connect each server, each
> database, and export user list...

I have nohing canned, but writing one in the programming language of
your choice should be a fairly simple task. You can use SQL-DMO, or
bypass that and connect directly throuhg ADO, ADO .Net or what you prefer.

If there is a master server that has all the other servers linked, you
could even do it in T-SQL.

The most difficult thing may be to find the list of servers, particularly
if there are different credentials per server. Certainly easier if you
connect to all servers with integrated security.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx