Report of every user for every database on server

For auditing purposes, I create a list of all the users who have access to
my databases. I do this by issuing this:
exec sp_MSForEachDB sp_helpuser
However, this doesn't include the name of the database that the list of
users is for. How can I include this info?
Thanks."Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
news:%23vGdjyRmFHA.2080@.TK2MSFTNGP10.phx.gbl...
> For auditing purposes, I create a list of all the users who have access to
> my databases. I do this by issuing this:
> exec sp_MSForEachDB sp_helpuser
> However, this doesn't include the name of the database that the list of
> users is for. How can I include this info?
> Thanks.
>
I use some dynamic sql and a cursor for this. In Query Analyzer, set your
output to Text (as opposed to grid).
BEGIN
SET NOCOUNT ON
-- Create some variables
DECLARE @.currentdb sysname, -- Current database that we are working with
@.sql varchar(4000) -- Dynamic SQL statement
-- Load the cursor with the current list of database names. Exclude SQL
Server databases.
DECLARE dbnames CURSOR FOR
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'Northwind',
'pubs')
ORDER BY [name] ASC
-- For each database, perform your query
OPEN dbnames
FETCH NEXT
FROM dbnames
INTO @.currentdb
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- The @.sql statement should be in the form of:
SET @.sql = @.currentdb + '.dbo.sp_helpuser'
-- Print a header to the messages tab
PRINT '=======================================
=================='
PRINT @.currentdb
PRINT ''
-- Execute the sql statement
EXECUTE(@.sql)
PRINT ''
PRINT '=======================================
=================='
PRINT ''
PRINT ''
-- Get the next database name
FETCH NEXT
FROM dbnames
INTO @.currentdb
END
-- Cleanup
CLOSE dbnames
DEALLOCATE dbnames
END
Rick Sawtell|||Nice script! However, it seems to have an issue with databases that begin
with the number zero (0). I get the folowing error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '0'.
Any ideas?
Thanks.|||"Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
news:uykxweSmFHA.572@.TK2MSFTNGP15.phx.gbl...
> Nice script! However, it seems to have an issue with databases that begin
> with the number zero (0). I get the folowing error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '0'.
> Any ideas?
> Thanks.
>
Ummm.. Find a better naming convention.. <wink>
I was under the impression that all object qualifiers in SQL Server 2000
require a letter as the first character, followed by numbers and some
symbols...
Guess I was wrong.
You could modify the script to include the square brackets. That may fix
it.
So adjust as follows:
Old line
SET @.sql = @.currentdb + '.dbo.sp_helpuser'
New line
SET @.sql = '[' + @.currentdb + '].dbo.sp_helpuser'
I should have probably done that in the first place for those who insist on
putting spaces etc. in their naming conventions.
Rick Sawtell|||Use QUOTENAME(@.currentdb) instead of @.currentdb
Steve Kass
Drew University
Joshua Campbell wrote:

>Nice script! However, it seems to have an issue with databases that begin
>with the number zero (0). I get the folowing error:
>Server: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near '0'.
>Any ideas?
>Thanks.
>
>|||It's not sufficient. Because I had to something very similar but including
all the permissions per each user using sp_helpuser
"Steve Kass" wrote:

> Use QUOTENAME(@.currentdb) instead of @.currentdb
> Steve Kass
> Drew University
> Joshua Campbell wrote:
>
>|||I'm sorry, but I don't understand you. If there is problem with
QUOTENAME, could you show sample code where it doesn't
work as expected?
SK
Enric wrote:
>It's not sufficient. Because I had to something very similar but including
>all the permissions per each user using sp_helpuser
>"Steve Kass" wrote:
>
>