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

Pages

Main Menu




Thursday, May 19, 2016

Anup Shah on WPF and Silverlight (Programming Garden): Create ASPNETDB database using aspnet_regsql tool

Anup Shah on WPF and Silverlight (Programming Garden): Create ASPNETDB database using aspnet_regsql tool: What is ASPNETDB? Microsoft has introduced a powerful default database schema in ASP.Net 2.0 as ASPNETDB.mdf database. This database fil...
Read More »

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










Read More »

Recent Posts

My Blog List