Getting User Access of all Users in a Database

Hi,
Had a requirement to refresh the database in dev from prod but the criteria was to retain the user access for that db intact. So needed to work on this as a priority. I have broken down the task in a two phases.

1st phase:
To identify the script to generate the present user access for the users in a database. Below is mentioned the script which will help me get the list.

select su.name as UserName ,sg.name as AccessGroup from sysmembers sm
join sysusers su on sm.memberuid=su.uid
join sysusers sg on sg.uid=sm.groupuid

2nd phase:
To identify the script which will help me restore the access for the users in dev enviroment after the database refresh.

select 'Exec sp_addrolemember ' +''''+ sg.name+ ''''+','+''''+ su.name+''''
from sysmembers sm
join sysusers su on sm.memberuid=su.uid
join sysusers sg on sg.uid=sm.groupuid


Tushar

Why no T-Log Backup possible in SIMPLE

When in SIMPLE recovery model a database log records in the active VLFs. These records are retained, as they may be required for a rollback operation. However, the inactive VLFs are truncated when a checkpoint occurs, meaning that the log records in these VLFs can be immediately overwritten with new log records. This is why a database operating in SIMPLE recovery is referred to as being in auto-truncate mode. In this mode, no "history" is maintained in the log and so it cannot be captured in a log backup and used as part of the restore process.

reference: sqlservercentral

Tushar

sp_lock and sp_who

Hi prepared a script to imitiate the sp_lock using DMV.

SELECT
tl.request_session_id as spid,
tl.resource_database_id as dbid,
tl.resource_associated_entity_id as objid,
tl.resource_type type,
tl.resource_description as resource,
tl.request_mode as mode,
tl.request_status as status
FROM sys.dm_tran_locks tl

Preparing a script for sp_who and sp_who2. Should be here soon.

Tushar

optimisng database maintenance plans

Fisrt step will be to identify the database files read,writes,i/o stats. For that we may utilise the below metioned queries.

1. To list NumberReads,NumberWrites,IOStallMS and Recovery_Model use the below mentioned script.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


CREATE TABLE #tbl_DB_FileStats
(
DatabaseName varchar(255),
FileName varchar(255),
NumberReads numeric(18,0),
NumberWrites numeric(18,0),
IOStallMS numeric(18,0)
)



EXECUTE master.dbo.sp_msforeachdb '
DECLARE @DBID int;
USE [?];
SET @DBID = DB_ID();
INSERT INTO #tbl_DB_FileStats
SELECT
DB_NAME(DBID) AS DatabaseName,
FILE_NAME(FileID) AS FileName,
NumberReads,
NumberWrites,
IoStallMS
FROM ::fn_virtualfilestats(@DBID, -1);'





create table #data (name sysname , db_size nvarchar(13), owner sysname, dbid smallint, created datetime, status varchar(600),
compatability_level tinyint)
insert into #data Exec sp_executesql N'sp_helpdb'

select name as 'Database',db_size,owner,substring(status,patindex ('%Recovery%',status)+9,patindex ('%, Ver%',status)- patindex ('%Recovery%',status)-9 ) as Recovery_Model
into #t1
from #data
where name not in ('model','master','tempdb','msdb')

select DatabaseName as 'Database' ,NumberReads,NumberWrites,IOStallMS
into #t2
from #tbl_DB_FileStats
where FileName not like '%log'
and DatabaseName not in ('model','master','tempdb','msdb')




select t1.[Database],t1.db_size,t1.[owner],t2.NumberReads,t2.NumberWrites,t2.IOStallMS,t1.Recovery_Model
from #t1 t1
inner join #t2 t2 on t1.[Database]=t2.[Database]




drop table #tbl_DB_FileStats
drop table #data
drop table #t1
drop table #t2

2. Database Index Fragmentation Information use the below mentioed script

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


CREATE TABLE #tbl_DB_IDX_Stats
(
[Database] sysname,
[Object] sysname,
[Index_ID] int,
[Index_Name] sysname,
avg_fragmentation_in_percent numeric(18,0)
)


EXECUTE master.dbo.sp_msforeachdb '
DECLARE @DBID int;
USE [?];
SET @DBID = DB_ID();
INSERT INTO #tbl_DB_IDX_Stats
SELECT db_name(ps.database_id) as [Database], Object_name(ps.OBJECT_ID) as [Object],
ps.index_id as [Index_ID], b.name as [Index_Name],ps.avg_fragmentation_in_percent as avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (@DBID, NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
and avg_fragmentation_in_percent>70
and name is not null
and @DBID not in (1,2,3,4)
ORDER BY ps.OBJECT_ID;'

select * from #tbl_DB_IDX_Stats

drop table #tbl_DB_IDX_Stats


Tushar

Identify the Recovery Model

Hi All,
In this post I will like to share a script which will generate a report about the detail of the databases on the server. The script can be modified to show more information as per requirement. I have used sp_helpdb to get all the information.

Script:
create table #data (name sysname , db_size nvarchar(13), owner sysname, dbid smallint, created datetime, status varchar(600),
compatability_level tinyint)
insert into #data Exec sp_executesql N'sp_helpdb'
select name as 'Database',db_size,owner,substring(status,patindex ('%Recovery%',status)+9,patindex ('%, Ver%',status)- patindex ('%Recovery%',status)-9 ) as Recovery_Model
from #data
where name not in ('model','master','tempdb','msdb')
drop table #data


Hope this helps a lot of accidental DBA's to perform thier task of reporting.

Tushar

SQL Server Backup and Restore History

Hi All.. This is one place I know most you must have knowledge about. Just mentioning this so that you all have one stop for the information.

To identify the backup history for the databases on the server.

SELECT a.name, b.type, MAX(b.backup_finish_date) LastSuccessfulBackup,
CAST((GETDATE() - MAX(b.backup_finish_date)) AS NUMERIC(5, 2)) IntervalInDays
FROM master..sysdatabases a
LEFT OUTER JOIN msdb..backupset b ON a.name = b.database_name
GROUP BY a.name, b.type
ORDER BY a.name, b.type

To identify the restore history of the databases on the server.
select restore_history_id,restore_date,destination_database_name,
user_name,restore_type,backup_set_id from msdb..restorehistory


Tushar

SQL Agent Service running under which account

Well. Today morning got an unusual requirement to identify the account under which the SQL SErver Agent Service is running. I did identify the undocumented stored procedure that helped me out in the work but also had an interesting finding.

If you want to know the aoount under which the SQL Server is running the try the below mentioed query.

DECLARE @ServiceaccountName varchar(250)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
N'ObjectName',
@ServiceAccountName OUTPUT,
N'no_output'

SELECT @ServiceaccountName

This gives the service account name for the SQL Server.

To identify the service account for the SQL Server Agent use the undoucmented stored procedure as is mentioned below.
exec msdb..sp_get_sqlagent_properties
Note that there might be some risk factors involved while using undocumented procedures in production.

Tushar