How to list all user accounts (NT and SQL) for a large number of databases
on a development server
Using sample code from MSDN library ("Finding Database Settings" by Ron
Talmage) and postings in various newsgroups, I created the following code
CoDevData.master.dbo.sp_helpuser '') AS A'
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.
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
Sr. Systems Engineer
VA Dept of Transportation