USP_EffectiveLoginPermissions

Procedure to show login permissions for all databases on a server.
Create the Procedure in master db or your tools database.

EXEC USP_EffectiveLoginPermissions ''
go

CREATE PROCEDURE USP_EffectiveLoginPermissions
          @User VARCHAR(100)
AS
  BEGIN
  
    SET NOCOUNT ON
    
    IF OBJECT_ID('tempdb..##ServerLogins') IS NOT NULL
      BEGIN
        DROP TABLE ##SERVERLOGINS
      END
      
    IF OBJECT_ID('tempdb..##DBUsers') IS NOT NULL
      BEGIN
        DROP TABLE ##DBUSERS
      END
      
    IF OBJECT_ID('tempdb..##DBList') IS NOT NULL
      BEGIN
        DROP TABLE ##DBLIST
      END
          
    IF OBJECT_ID('tempdb..##Results') IS NOT NULL
      BEGIN
        DROP TABLE ##RESULTS
      END
      
    DECLARE  @DBName VARCHAR(128);    
    DECLARE  @SQLCmd VARCHAR(2000);    
    DECLARE  @NumberOfDBs INT;
    
    -- Get the SQL Server logins
    -- Create login table
    CREATE TABLE ##SERVERLOGINS (
  [SID] VARBINARY(85) NULL,
  [LOGIN_NAME] VARCHAR(128) NULL);
    
    -- Populate login table
    INSERT INTO ##SERVERLOGINS
    SELECT SID,CAST(LOGINNAME AS VARCHAR(128))  AS [LOGIN_NAME]
    FROM   MASTER.DBO.SYSLOGINS
    
    
    -- Create list of databases
    CREATE TABLE ##DBLIST (
  [DBNAME] VARCHAR(128))
    
    -- perform for all dbs on server
    INSERT INTO ##DBLIST
    SELECT   [NAME]
    FROM     MASTER..SYSDATABASES
    WHERE    [DBID] > 4
    ORDER BY [NAME];
    
    SELECT @NumberOfDBs = COUNT(* )
    FROM   ##DBLIST
           
    -- Create the output table for the Database User ID's
    CREATE TABLE ##DBUSERS (
  [DATABASE_USER_ID] VARCHAR(128),
  [SERVER_LOGIN] VARCHAR(128),
  [DATABASE_ROLE] VARCHAR(128),
  [DATABASE_NAME] VARCHAR(128));  
      
    -- Declare a cursor to loop through all the databases on the server
    DECLARE CSRDB CURSOR  FOR
  SELECT   [DBNAME]
  FROM     ##DBLIST
  ORDER BY [DBNAME]
             
    -- Open the cursor and get the first database name
    OPEN CSRDB
    
    FETCH NEXT FROM CSRDB
    INTO @DBName
         
    -- Loop through the cursor
    WHILE @@FETCH_STATUS = 0
      BEGIN      
        -- populate ##DBUsers table for current db
        SELECT @SQLCmd = 'INSERT ##DBUsers ' 
   + ' SELECT CAST(su.[name] AS VARCHAR(128)) AS [database_user_id], ' 
   + ' CAST(COALESCE (u.[login_name], ''* Orphaned *'') AS VARCHAR(128))AS [server_login], ' 
   + ' CAST(COALESCE (sug.name, ''Public'') AS VARCHAR(128)) AS [database_role],' 
   + ' CAST(''' + @DBName + ''' AS VARCHAR(128)) AS [database_name]' 
   + ' FROM [' + @DBName + '].[dbo].[sysusers] su' 
   + ' LEFT OUTER JOIN ##ServerLogins u' + ' ON su.sid = u.sid' 
   + ' LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm ' 
   + ' INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug ' 
   + ' ON sm.groupuid = sug.uid)' 
   + ' ON su.uid = sm.memberuid ' 
   + ' WHERE su.hasdbaccess = 1' 

IF LTRIM(RTRIM(@User)) <> ''
  SELECT @SQLCmd = @SQLCmd + ' AND SU.name = ''' + @User + ''''

   -- uncomment to debug       
        -- PRINT @SQLCmd
        EXEC(@SQLCmd)
        
        -- Get the next database name
        FETCH NEXT FROM CSRDB
        INTO @DBName
             
      -- End of the cursor loop
      END
      
    -- Close and deallocate the CURSOR
    CLOSE CSRDB
    
    DEALLOCATE CSRDB
             
    CREATE TABLE ##RESULTS (
        [INSTANCE_NAME] VARCHAR(128),
  [DATABASE_NAME] VARCHAR(128),
  [OBJTYPE] VARCHAR(128),
  [LOGIN_STATUS] VARCHAR(128),
  [USERGRANTEE] VARCHAR(128));
    
    TRUNCATE TABLE ##RESULTS
    
    --data for logins
    INSERT INTO ##RESULTS
    SELECT 
  CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) AS INSTANCE_NAME, 
  DATABASE_NAME,
  MAX(CASE [DATABASE_ROLE] WHEN 'db_datareader' THEN 'db_datareader, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_accessadmin' THEN 'db_accessadmin, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_backupoperator' THEN 'db_backupoperator, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_datawriter' THEN 'db_datawriter, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_ddladmin' THEN 'db_ddladmin, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_denydatareader' THEN 'db_denydatareader, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_denydatawriter' THEN 'db_denydatawriter, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_owner' THEN 'db_owner, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_securityadmin' THEN 'db_securityadmin, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'public' THEN 'public, ' ELSE '' END) AS ROLE_OBJECTTYPE,
  CASE [SERVER_LOGIN] WHEN '* Orphaned *' THEN [SERVER_LOGIN] ELSE 'OK' END AS LOGIN_STATUS,
        CASE WHEN [DATABASE_USER_ID] = [SERVER_LOGIN] THEN [DATABASE_USER_ID] 
    WHEN [SERVER_LOGIN] = '* Orphaned *' THEN [DATABASE_USER_ID] 
    ELSE [DATABASE_USER_ID] + ' (' + [SERVER_LOGIN] + ')' END AS USER_GRANTEE
  
    FROM     
  ##DBUSERS
    GROUP BY 
  [DATABASE_NAME],
  [DATABASE_USER_ID],
  [SERVER_LOGIN]
                            
    SELECT   
  [INSTANCE_NAME], 
  [DATABASE_NAME],
  [USERGRANTEE],
  LEFT(OBJTYPE,LEN([OBJTYPE])-1) AS ROLES,
  [LOGIN_STATUS]
    FROM     
  ##RESULTS
    ORDER BY 
  1, 2, 3
  END
  go