Very Computer

How to list all user accounts (NT and SQL) for a large number of databases
on a development server

Proposed Solution:

Using sample code from MSDN library ("Finding Database Settings" by Ron
Talmage) and postings in various newsgroups, I created the following code

 FROM master.dbo.sysdatabases)

BEGIN


  FROM
OPENROWSET(''SQLOLEDB'',''SERVER=(local)'';''Trusted_Connection=yes'',

CoDevData.master.dbo.sp_helpuser '') AS A'

END

The table dbusers is created with three columns. I initially added the
server as a linked server to itself, in case that was the problem, but no
change was noticed. The multiple quotes are all actually single quotes, as
specified n the Talmage article.

Error:

Above code creates infinite loop, generating an "incorrect syntax near ','"
referencing the FROM statement.  I am not sure that the error is actually
there, and I have gone over the code numerous times, trying various methods
of specifying the connect string, etc.  Without the ''SET FMTONLY OFF"
statement, the Temp table comes back as an invalid object.

If I remove the quotes from around the ';', passing the connect string as
one string, I get
"Server user 'NT AUTHORITY\SYSTEM' is not a valid user in database" .

If I specify the connect string as
(''SQLOLEDB'',''CoDevData'';''sa'';''*******'',  I get
"Could not process object 'SET FMTONLY OFF USE A26Dev EXEC
CoDevData.master.dbo.sp_helpuser '. The OLE DB provider 'SQLOLEDB' indicates
that the object has no columns."

Does anyone have any ideas?  Or is there a vastly simpler way to do this
than I have created for myself?  Any and all suggestions are welcome

John Willard
Sr. Systems Engineer
DBA
VA Dept of Transportation

you can use a cursor (for select name from master.dbo.sysdatabases) to go
through the databases and then insert into your dbusers by selecting from
respective sysusers.  As you did, dynamic sql is used to construct the
insert/select statements.

hth

Quentin

Re "Server user 'NT AUTHORITY\SYSTEM' is not a valid user in database": The
SQL Server service appears to be started using the LocalSystem account (aka
NT's builtin SYSTEM account), and the the trusted connection back to SQL
Server will use that startup account, thus I suspect someone had previously
followed http://support.microsoft.com/?id=263712. This is one "Achilles
heal" for the OPENROWSET/loopback approach within your succinct code <g>.

Re "If I specify the connect string as": This is a variation of
http://support.microsoft.com/?id=270119 where that article's workarounds
won't help <g>. While a SELECT NULL  preceding  the EXEC may allow the
FMTONLY to get the metadata, the above " Achilles heal" is still applicable.

My choice would be to harness the common ability of sp_helpdb, sp_helpuser
and sp_helplogins: They all return result sets. The latter 2 result sets
include the SID (which is the join field between them). Also, you may want
to consider http://support.microsoft.com/?id=262499 (as opposed to using
EXEC).

And if T-SQL isn't a requirement, SQL-DMO can return the same result sets.
Even if T-SQL is required you could still use sp_OA% with SQLDMO, but this
approach also has that "Achilles heal".

Other T-SQL examples (that would need to be edited) are in
http://support.microsoft.com/?id=310882,
http://support.microsoft.com/?id=331450,
http://support.microsoft.com/?id=331451. However, I think it would be best
to avoid directly selecting against system tables when there are system
stored procedures (or information_schema) that can perform the same duty.
There is no telling what will happen to the ability to use a system table
in the next version of SQL Server <g>.

And while there are a couple of undocumented stored procedures that may
help you, I cannot suggest that you *execute* them. I cannot suggest
execution because (being undocumented), their behavior/results may change
at anytime without any advanced notice, and  (being undocumented) you will
not have the ability to request that we revert their behavior (because
their behavior is undocumented <g>). However, I see no problem with you
running "sp_helptext sp_MSforeachdb", and (based upon what is reported by
that first helptext) running "sp_helptext sp_MSforeach_worker". You can
copy their code into your own stored procedures (and test as the scripts'
comments indicate some objects may need to be reverse-enginneered <g>).
From my quick review of their text/scripts, it appears everything *within*
those undocumented stored procedures is documented (and thus what is
*within* those undocumented procedures appears to be completely supported).
Global variables are yet another possibility.

Depending upon need, you also can script the database using SEM, SQL DMO,
or http://support.microsoft.com/?id=220163, or, you can create the users in
the model database.

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.