StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I hacked up the sp_helplogins system stored procedure to return close to what you're looking for.</p> <p>I can't get the script to paste correctly so here is a <a href="http://cid-c58c2473d934ad72.office.live.com/self.aspx/Clipboard/StackOverflow5523114.sql" rel="nofollow">link</a> to the script.</p> <pre><code>DECLARE @LoginNamePattern sysname = NULL set nocount on declare @exec_stmt nvarchar(3550) declare @CountSkipPossUsers int ,@Int1 int declare @c10DBName sysname ,@c10DBStatus int ,@c10DBSID varbinary(85) declare @charMaxLenLoginName varchar(11) ,@charMaxLenDBName varchar(11) ,@charMaxLenUserName varchar(11) declare @DBOptLoading int --0x0020 32 "DoNotRecover" ,@DBOptPreRecovery int --0x0040 64 ,@DBOptRecovering int --0x0080 128 ,@DBOptSuspect int --0x0100 256 ("not recovered") ,@DBOptOffline int --0x0200 512 ,@DBOptDBOUseOnly int --0x0800 2048 ,@DBOptSingleUser int --0x1000 4096 ------------- create work holding tables ---------------- -- Create temp tables before any DML to ensure dynamic CREATE TABLE #tb1_UA ( LoginName sysname collate database_default NOT Null ,DBName sysname collate database_default NOT Null ,UserName sysname collate database_default NOT Null ) --------------- Cursor, for DBNames ------------------- declare ms_crs_10_DB Cursor local static For select name ,status ,sid from master.dbo.sysdatabases OPEN ms_crs_10_DB ----------------- LOOP 10: thru Databases ------------------ WHILE (10 = 10) begin --LOOP 10: thru Databases FETCH next from ms_crs_10_DB into @c10DBName ,@c10DBStatus ,@c10DBSID IF (@@fetch_status <> 0) begin deallocate ms_crs_10_DB BREAK end -------------------- Okay if we peek inside this DB now? IF ( @c10DBStatus & @DBOptDBOUseOnly > 0 AND @c10DBSID <> suser_sid() ) begin select @CountSkipPossUsers = @CountSkipPossUsers + 1 CONTINUE end IF (@c10DBStatus & @DBOptSingleUser > 0) begin select @Int1 = count(*) from sys.dm_exec_requests where session_id <> @@spid and database_id = db_id(@c10DBName) IF (@Int1 > 0) begin select @CountSkipPossUsers = @CountSkipPossUsers + 1 CONTINUE end end IF (@c10DBStatus & ( @DBOptLoading | @DBOptRecovering | @DBOptSuspect | @DBOptPreRecovery ) > 0 ) begin select @CountSkipPossUsers = @CountSkipPossUsers + 1 CONTINUE end IF (@c10DBStatus & ( @DBOptOffline ) > 0 ) begin --select @CountSkipPossUsers = @CountSkipPossUsers + 1 CONTINUE end IF (has_dbaccess(@c10DBName) <> 1) begin raiserror(15622,-1,-1, @c10DBName) CONTINUE end --------------------- Add the User info to holding table. select @exec_stmt = ' INSERT #tb1_UA ( DBName ,LoginName ,UserName ) select N' + quotename(@c10DBName, '''') + ' ,l.name ,u2.name from ' + quotename(@c10DBName, '[')+ '.sys.database_role_members m ,' + quotename(@c10DBName, '[')+ '.sys.database_principals u1 ,' + quotename(@c10DBName, '[')+ '.sys.database_principals u2 ,sys.server_principals l where u1.sid = l.sid and m.member_principal_id = u1.principal_id and m.role_principal_id = u2.principal_id' + case when @LoginNamePattern is null then '' else ' and ( l.name = N' + quotename(@LoginNamePattern , '''') + ' or l.name = N' + quotename(@LoginNamePattern , '''') + ')' end EXECUTE(@exec_stmt) end --loop 10 ------------ Optimize UA report column display widths ----------- select @charMaxLenLoginName = convert ( varchar ,isnull ( max(datalength(LoginName)) ,9) ) ,@charMaxLenDBName = convert ( varchar ,isnull ( max(datalength(DBName)) ,6) ) ,@charMaxLenUserName = convert ( varchar ,isnull ( max(datalength(UserName)) ,8) ) from #tb1_UA ------------ Print out the UserOrAlias report ------------ EXEC( ' set nocount off select ''LoginName'' = substring (LoginName ,1 ,' + @charMaxLenLoginName + ') ,''DBName'' = substring (DBName ,1 ,' + @charMaxLenDBName + ') ,''MemberOf'' = substring (UserName ,1 ,' + @charMaxLenUserName + ') from #tb1_UA order by 1 ,2 ,3 Set nocount on ' ) DROP Table #tb1_UA </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload