Users and permissions in Database

Below Script is used to find the Users in Database and their permissions

SQL Server 2000

use [master]

select db_name(0)AS DBName, a.name, CASE WHEN a.isntgroup =1 AND a.isntuser=0 THEN 'Windows Group'

    WHEN a.isntgroup =0 AND a.isntuser=1 THEN 'Windows Login'

    WHEN a.issqlrole=1 THEN 'Database Role'

    ELSE 'SQL Login' END AS 'Login Type',USER_NAME(b.groupuid) AS 'AssociatedRole' ,sl.name 'Login_ID'

from  master..syslogins sl join dbo.sysusers a  on a.sid=sl.sid   LEFT OUTER JOIN dbo.sysmembers b ON a.uid=b.memberuid where a.altuid<>1 and a.uid not in (1,2) AND 'DBName' NOT IN ('master','msdb','model','tempdb') ORDER BY a.Name

SQL Server 2008

use [master]

Select db_name(0) As DBName,a.name 'User_Name',

CASE WHEN a.isntgroup =1 AND a.isntuser=0 THEN 'Windows Group'

    WHEN a.isntgroup =0 AND a.isntuser=1 THEN 'Windows Login'

    WHEN a.issqlrole=1 THEN 'Database Role'

    ELSE 'SQL Login' END AS 'Login Type',USER_NAME(b.groupuid) AS 'AssociatedRole' ,sl.name 'Login_ID'

from master..syslogins sl join sysusers a on sl.sid=a.sid

LEFT OUTER JOIN sysmembers b ON a.uid=b.memberuid

--where a.altuid<>1

SQL Server 2008

use master

Select db_name(0) As DBName,a.name 'User_Name',

CASE WHEN a.isntgroup =1 AND a.isntuser=0 THEN 'Windows Group'

    WHEN a.isntgroup =0 AND a.isntuser=1 THEN 'Windows Login'

    WHEN a.issqlrole=1 THEN 'Database Role'

    ELSE 'SQL Login' END AS 'Login Type',USER_NAME(b.groupuid) AS 'AssociatedRole' ,sl.name 'Login_ID',

    default_schema_name

from master..syslogins sl join sysusers a on sl.sid=a.sid

LEFT OUTER JOIN sysmembers b ON a.uid=b.memberuid

left join sys.database_principals dp on dp.principal_id=a.uid

--where a.altuid<>1

Script to find User Permissions on all DB's in a SQL Server 

create table #DBuserperm (DBName varchar(100),User_Name varchar(100),Login_Type varchar(100)

,Associated_Role varchar(100),LoginID varchar(100),Default_Schema_Name varchar(100),)

declare @dbname varchar(100)

declare @sqlQuery nvarchar(4000)

declare dbcursor CURSOR for

select name from sys.databases

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

OPEN dbcursor

FETCH NEXT FROM dbcursor

into @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

set @sqlQuery='Use ['+@dbname+']'

      set @sqlQuery =@sqlQuery+ 'Select db_name(0) As DBName,a.name '''+'User_Name'+''',

CASE WHEN a.isntgroup =1 AND a.isntuser=0 THEN '''+'Windows Group'+'''

    WHEN a.isntgroup =0 AND a.isntuser=1 THEN '''+'Windows Login'+'''

    WHEN a.issqlrole=1 THEN '''+'Database Role'+'''

    ELSE '''+'SQL Login'+''' END AS '''+'Login Type'+''',USER_NAME(b.groupuid) AS '''+'AssociatedRole'+''' ,

    sl.name '''+'Login_ID'+''',

    default_schema_name

from master..syslogins sl join sysusers a on sl.sid=a.sid

LEFT OUTER JOIN sysmembers b ON a.uid=b.memberuid

left join sys.database_principals dp on dp.principal_id=a.uid'

insert into #DBuserperm

      exec sp_executesql @sqlQuery 

FETCH NEXT FROM dbcursor

into @dbname

END

select * from #DBuserperm

drop table #DBuserperm

CLOSE dbcursor

Deallocate dbcursor

SQL Server Detailed Configuration Information

Below script is used to find the detailed information about the configuration of SQL Server

create table #SVer(ID int,  Name  sysname, Internal_Value int, Value nvarchar(512))

insert #SVer exec master.dbo.xp_msver

declare @SmoRoot nvarchar(512)

exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT

declare @HkeyLocal nvarchar(18)

declare @ServicesRegPath nvarchar(34)

declare @SqlServiceRegPath sysname

declare @BrowserServiceRegPath sysname

declare @MSSqlServerRegPath nvarchar(31)

declare @InstanceNamesRegPath nvarchar(59)

declare @InstanceRegPath sysname

declare @SetupRegPath sysname

declare @NpRegPath sysname

declare @TcpRegPath sysname

declare @RegPathParams sysname

declare @FilestreamRegPath sysname

select @HkeyLocal=N'HKEY_LOCAL_MACHINE'

-- Instance-based paths

select @MSSqlServerRegPath=N'SOFTWARE\Microsoft\MSSQLServer'

select @InstanceRegPath=@MSSqlServerRegPath + N'\MSSQLServer'

select @FilestreamRegPath=@InstanceRegPath + N'\Filestream'

select @SetupRegPath=@MSSqlServerRegPath + N'\Setup'

select @RegPathParams=@InstanceRegPath+'\Parameters'

-- Services

select @ServicesRegPath=N'SYSTEM\CurrentControlSet\Services'

select @SqlServiceRegPath=@ServicesRegPath + N'\MSSQLSERVER'

select @BrowserServiceRegPath=@ServicesRegPath + N'\SQLBrowser'

-- InstanceId setting

select @InstanceNamesRegPath=N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

declare @InstallSqlDataDir nvarchar(512)

exec master.dbo.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLDataRoot', @InstallSqlDataDir OUTPUT

declare @BackupDirectory nvarchar(512)

exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'BackupDirectory', @BackupDirectory OUTPUT

create table #serverinfo (Name varchar(100), Value sql_variant)

insert #serverinfo

select 'Machine Name' Name,serverproperty('Machinename') Value

union all

select 'Server Name',@@Servername

union all

select name,value

from #SVer

where name in ('ProductVersion','Language','PhysicalMemory','ProcessorCount','FileDescription')

union all

select 'RootDirectory',@SmoRoot

union all

select name,value_in_use

from sys.configurations

where

name in ('max server memory (MB)','max degree of parallelism','cost threshold for parallelism',

'default trace enabled','Database Mail XPs')

union all

select 'Edition',serverproperty('Edition')

union all

select 'Product Level',serverproperty('productlevel')

union all

select 'Version',replace(substring(@@version,1,charindex('-', (@@version))),'-','')

union all

select 'collation',serverproperty('collation')

union all

select 'IsCaseSensitive',CAST(case when 'a' <> 'A' then 1 else 0 end AS bit)

union all

select 'ResourceVersionString',SERVERPROPERTY(N'ResourceVersion')

union all

select 'ResourceLastUpdateDateTime',SERVERPROPERTY(N'ResourceLastUpdateDateTime')

union all

select 'IsClustered',serverproperty('isclustered')

union all

select 'IsIntegratedSecurityOnly',serverproperty('IsIntegratedSecurityOnly')

union all

select 'IsSingleUser',serverproperty('IsSingleUser')

union all

select 'SQLDataRoot', @InstallSqlDataDir

union all

select 'BackupDirectory', @BackupDirectory

union all

select name,enabled from msdb.dbo.sysjobs

union all

select name,'EMail ID:- '+email_address+' Net Send :-'+isnull(netsend_address,' ') from msdb.dbo.sysoperators

union all

select name,has_notification from msdb.dbo.sysalerts

select * from #serverinfo

Drop table #SVer,#serverinfo

Script to find Index Details

--- Below query is used to find the Index Details in a Database

select DB_NAME(a.database_id) DBName,OBJECT_NAME(a.object_id) Obj_Name,

*

from

sys.dm_db_index_physical_stats(db_id('mywork'),NULL,NULL,NULL,'LIMITED') As a

GO

--- Below query is used to find the Detailed Index Details in a Database

select DB_NAME(a.database_id) DBName,OBJECT_NAME(a.object_id) Obj_Name,i.name,

a.avg_fragmentation_in_percent,a.fragment_count,a.avg_fragment_size_in_pages,

a.page_count,a.record_count,a.avg_page_space_used_in_percent,

a.index_id,a.index_depth,a.index_level,

a.partition_number,a.index_type_desc,a.alloc_unit_type_desc,

a.min_record_size_in_bytes,a.max_record_size_in_bytes,

a.avg_record_size_in_bytes,a.compressed_page_count

from

sys.dm_db_index_physical_stats(db_id('mywork'),NULL,NULL,NULL,'DETAILED') As a

JOIN sys.indexes AS i

ON a.object_id = i.object_id AND a.index_id = i.index_id

GO

--- DMV to check the Index Usage Statistics

select DB_NAME(database_id) DBName,OBJECT_NAME(object_id) Obj_Name,

* from sys.dm_db_index_usage_stats

where DB_NAME(database_id)='mywork'

order by last_user_scan desc

GO

TableSpace Utilization Report

Below  Script is used to find the Tablespace utilization details in a Oracle Database

select a.tablespace_name,a.Total_Size_MB,b.Free_Size_MB,

(a.Total_Size_MB-b.Free_Size_MB) Used_Size_MB,

round(((a.Total_Size_MB-b.Free_Size_MB)/a.Total_Size_MB)*100,2) Pct_Used,

round((b.Free_Size_MB/a.Total_Size_MB)*100,2) Pct_Free

--,b.tablespace_name

from

(select tablespace_name,sum(bytes/1024/1024) Total_Size_MB

from dba_data_files

group by tablespace_name) a,

(select  tablespace_name,sum(bytes/1024/1024) Free_Size_MB from dba_free_space

group by tablespace_name) b

where a.tablespace_name=b.tablespace_name

union all

select d.tablespace_name,

(d.free_mb+d.used_mb) total_mb,

d.free_mb,d.used_mb,

(d.used_mb/(d.free_mb+d.used_mb))*100,(d.free_mb/(d.free_mb+d.used_mb))*100

from

(select tablespace_name,round(sum(bytes_free/1024/1024),2) free_mb,

round(sum(bytes_used/1024/1024),2) used_mb

from v$temp_space_header

group by tablespace_name) d

 

TSQL to find Time lag between Principal and Mirror DB

---Below Script is used to find the time of oldest transaction log which was last sent to Mirroring DB (Time lag between Principal and Mirror DB)

--Below Script will append the Database mirroring to monitor table

--if the same is not updated in last 15 seconds (approx)

create table #dbmmonitor

(database_name varchar(100),role varchar(100),

mirroring_state varchar(100),witness_status varchar(100),

log_generation_rate int,unsent_log int,send_rate int,

unrestored_log int,recovery_rate int,transaction_delay int,transactions_per_sec int,

average_delay int,time_recorded datetime,time_behind datetime,local_time datetime)

insert into #dbmmonitor

Exec msdb..sp_dbmmonitorresults 'dbmir',0,1

declare @time_recorded datetime, @time_behind datetime

select @time_recorded=time_recorded,@time_behind=time_behind from #dbmmonitor

declare @hour int,@min int,@sec int,@day int

declare @old_unsent_trn varchar(100)

select @hour=(DATEDIFF(hh,@time_behind,@time_recorded))%24

select @min=(DATEDIFF(mi,@time_behind,@time_recorded))%60

select @sec=(DATEDIFF(ss,@time_behind,@time_recorded))%60

select @day=(DATEDIFF(ss,@time_behind,@time_recorded))/86400

select @old_unsent_trn=convert(varchar(20),@day)+':'+convert(varchar(20),@hour)+':'+convert(varchar(20),@min)+':'+convert(varchar(20),@sec)

select @old_unsent_trn 'Oldest Unsent Transaction'

drop table #dbmmonitor

GO

---Using msdb.dbo.dbm_monitor_data table for results

exec msdb.dbo.sp_dbmmonitorresults 'dbmir',0,1

---

declare @local_time1 datetime,@end_of_log_lsn1 bigint,@failover_lsn1 bigint

declare @local_time2 datetime,@end_of_log_lsn2 bigint,@failover_lsn2 bigint

declare @min int,@hour int,@day int,@min1 int

declare @old_unsent_trn varchar(100)

select top(1) @local_time1=local_time,@end_of_log_lsn1=end_of_log_lsn,

@failover_lsn1=failover_lsn

from msdb.dbo.dbm_monitor_data

order by local_time desc

--select @local_time1 local_time1,@end_of_log_lsn1 end_of_log_lsn1

--,@failover_lsn1 failover_lsn1

select top(1) @local_time2=local_time,@end_of_log_lsn2=end_of_log_lsn,

@failover_lsn2=failover_lsn

from  msdb.dbo.dbm_monitor_data

where end_of_log_lsn=@failover_lsn1

order by local_time desc

--select @local_time2 local_time2,@end_of_log_lsn2 end_of_log_lsn2

--,@failover_lsn2 failover_lsn2

select @day=(DATEDIFF(ss,@local_time2,@local_time1))/86400

select @min1=DATEDIFF(mi,@local_time2,@local_time1)

select @hour=convert(int,@min1)/60

select @min=convert(int,@min1)%60

--select @day,@hour,@min1

select @old_unsent_trn=convert(varchar(20),@day)+':'+convert(varchar(20),@hour)+':'+convert(varchar(20),@min)

select @old_unsent_trn 'Oldest Unsent Transaction'