/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu




Tuesday, May 3, 2016

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










6 comments:

  1. Recruitment Notification is one of the Top most Job Portal Govt jobs in AP 2017 there

    ReplyDelete
  2. Sarkari Recruitment is one of the biggest Indian Job Site so here you will getTNPSC Jobs 2017so

    ReplyDelete
  3. Thanks for providing such nice information to us. It provides such amazing information on Care/as well
    Health/.The post is really helpful and very much thanks to you. The information can be really helpful on health, care as well as on
    Exam/ tips. The post is really helpful.
    Thanks for providing such nice information to us. It provides such amazing information on MedicalExams/

    ReplyDelete
  4. Calculate Income Tax Liability Educational Cess, Surcharge, Eligible Deductions, taxable Income, Slab-wise Income of Individuals with Incometax calculators.
    Incometax calculator

    ReplyDelete
  5. Get all Assam Government Jobs Notifications here for all qualifications like 10th, 12th, technical and Any Degree etc. and latest Job updates.
    Assam Government Jobs

    ReplyDelete

Recent Posts

My Blog List