Use T-SQL to get a list of users and roles for all databases at a server!

DECLARE @UsersAndRoles TABLE
(
   db varchar(50),
   access varchar(4),
   name varchar(255)
)
declare @db varchar(50)
declare @sql varchar(255)
DECLARE curs CURSOR FOR SELECT name FROM master.sys.databases WHERE name NOT IN ('master','tempdb','model','msdb') order by name
OPEN curs
FETCH NEXT FROM curs into @db
WHILE @@FETCH_STATUS = 0
   BEGIN
      BEGIN TRY
         select @sql = 'USE ' + @db + '; SELECT db=''' + @db + ''', access=''user'', name
               FROM sysusers WHERE (islogin = 1 AND isntname = 1) OR issqluser = 1
               ORDER BY name'
         INSERT INTO @UsersAndRoles EXEC sp_sqlexec @sql
         select @sql = 'USE ' + @db + '; SELECT db=''' + @db + ''', access=''role'', name
               FROM sysusers WHERE issqlrole = 1 ORDER BY name'
         INSERT INTO @UsersAndRoles EXEC sp_sqlexec @sql
      END TRY
      BEGIN CATCH
         INSERT INTO @UsersAndRoles(db,access,name) VALUES (@db, 'FAIL', 'no
               access to inspect')
      END CATCH
      FETCH NEXT FROM curs into @db
   END
CLOSE curs
DEALLOCATE curs
SELECT * FROM @UsersAndRoles ORDER BY db

 
 

This is the only way to use a magic string with a USE statement. You have to put SQL in a magic string and then call sp_sqlexec unfortunately.