SQL SERVER QUERY Part-1 Script to find database role given to logins in all the databases.

/*
Description     : Script to find database role given to logins in all the databases.
Parameters    : NONE.
Returns    : NA
Create date : 4/May/2016
*/

     create table ##t2(
     DBname varchar(300),
     DBrole_name varchar(300),
     Type_dbrole varchar(300),
     Grantee varchar(300))

            insert into ##t2 exec
                        sp_msforeachdb 'use [?];
                        select  db_name() [DB name],prin.name,prin.type_desc,un.name from sys.database_role_members rl left join
                        sys .database_principals prin on rl.role_principal_id =prin.principal_id
                        join sys.sysusers un on rl.member_principal_id=un.uid'

      select DBname,Grantee,DBrole_name,Type_dbrole from  ##t2
 /* where Grantee like '%provide_the_login_name%' */
 order by DBname,Grantee

      drop table ##t2

_________________________________________________________________________________

1. All Database User accounts and all permission granted on a specific DB.
2. Permissions of a specific DB User on a specific DB.
3. All permissions that a certain login account possesses on all DBs in the instances.

CREATE proc [dbo].[DBASP_ALL_UserPermission]

AS

DECLARE @strSQL nvarchar(2000),

@dbname nvarchar(256)

IF OBJECT_ID('tempdb..#DBUsers') IS NOT NULL DROP TABLE #DBUsers

CREATE table #DBUsers 

(

DBname varchar (256),  

LoginName varchar(100),  

DBUserName varchar(100),           

[DBRole] varchar (100),     

PrincipalType  varchar(100), 

PermissionName  varchar(100) ,

ObjectType varchar(50),  

Objectname varchar(100), 

Columnname varchar(100)

)  

DECLARE listdbs Cursor

FOR

SELECT name from master.dbo.sysdatabases

WHERE  name not in ('master', 'model', 'msdb', 'tempdb')

OPEN listdbs

FETCH next

     FROM  listdbs into @dbname    

     WHILE @@fetch_status = 0

     BEGIN   

     SELECT @strSQL =                      

    '

     Use ['+ @dbname+'] ;

     SELECT 

      DB_name()

     ,sp.name 

     ,dp.name    

     ,dp2.name

     ,dp.type_desc

     ,perm.permission_name

     , objectType = case perm.class

             WHEN 1 THEN obj.type_desc

                      ELSE perm.class_desc

     END

     ,objectName = case perm.class

              when 1 then Object_name(perm.major_id)

                    when 3 then schem.name 

                             when 4 then imp.name

     END

                     , col.name

     FROM

     sys.database_role_members drm

     RIGHT JOIN  sys.database_principals dp

     on dp.principal_id = drm.member_principal_id

     LEFT JOIN sys.database_principals dp2

     on dp2.principal_id = drm.role_principal_id

     FULL JOIN sys.server_principals sp 

     ON dp.[sid] = sp.[sid] 

     LEFT JOIN sys.database_permissions perm 

     ON perm.[grantee_principal_id] = dp.[principal_id]

     LEFT JOIN sys.columns col 

     ON col.[object_id] = perm.major_id 

     AND col.[column_id] = perm.[minor_id] 

     LEFT JOIN sys.objects obj 

     ON perm.[major_id] = obj.[object_id] 

     LEFT JOIN sys.schemas schem 

     ON schem.[schema_id] = perm.[major_id] 

     LEFT JOIN sys.database_principals imp 

     ON imp.[principal_id] = perm.[major_id] 

     WHERE dp.name not in (''sys'' , ''information_schema'' , ''guest'', ''public'')

     ORDER by sp.name

    '

    INSERT into #DBUsers

    EXEC (@strSQL)

    FETCH NEXT

    FROM listdbs into @dbname

    END

    CLOSE listdbs

    DEALLOCATE listdbs

    SELECT * from #DBUsers 

    GO

    

EXEC DBASP_ALL_UserPermission