TSQL to Get all details of who created the SQL Server Login on a SQL server Instance

As a DBA, One of the most important task is to audit the security.

Very Often, we may come across situation where logins are created on SQL server Instance but we are not aware of any details on who created it, when it was created etc.

So here a small simple query to all such details.

Firstly we need to get the path of default Trace which is running on SQL Server Instance,
The below query will assist you in getting that path.

select value from ::fn_trace_getinfo(0)

Now you will get values something like below. The Path may vary how you have configured it to be and it will show you the default trace which are currently running.

Now copy the path which you have found out in above query and replace "PlacetheTraceFilePathwhichyouhavegotfromabovequeryhere" with the path which you have found in the below query to get the required details.

SELECT  ste.name
        ,sftgt.DatabaseName 
        ,sftgt.NTDomainName 
        ,sftgt.ApplicationName 
        ,sftgt.LoginName 
        ,sftgt.StartTime 
        ,sftgt.TargetLoginName 
        ,sftgt.SessionLoginName
FROM    sys.fn_trace_gettable('PlacetheTraceFilePathwhichyouhavegotfromabovequeryhere', DEFAULT) sftgt
        JOIN sys.trace_events ste ON sftgt.EventClass = ste.trace_event_id
        JOIN sys.trace_subclass_values stsv ON stsv.trace_event_id = ste.trace_event_id
                                            AND stsv.subclass_value = sftgt.EventSubClass
WHERE   ste.name = 'Audit Addlogin Event'
        AND stsv.subclass_name = 'add'
GO

You can customize this query.
Note: This feature is available from SQL Server 2005 onwards only.

You get many more information from default trace. Will cover more in further posts.

T-SQL to backup all databases or selected databases to specific location

Very often we come across situation where we need to backup database in bulk. Sometime when you are migrating a server or upgrading to different version or applying patch and to be on safer side you tend to take backup of all database. imagine you have more than 100 databases on server and backing up each database would be tedious task

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name 

--Provide the path where all the databases needs to be backed up
SET @path = 'MyBackupFilePath'  

--used to suffix the current date at the end of backup filename
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE db_cursor CURSOR FOR  

--Use this for all database except the system databases and any exclusion you can make
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')  

--Uncomment and use this for only specific databases.
--Those database names you can provide under IN clause
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name IN ('MyDB1','MyDB2') 


OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName WITH STATS = 1  

       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor 

Script to change collation of multiple columns in multiple table for all tables in database

Various time we come across situation where in lower environment the collation will be different and in production environment the collation will be different. This might hamper you work in many ways. Your development work may come to halt or slowdown making these changes.

So here is a small script to change collation of various columns for all table. If you want for some columns and some table the select statement in cursor needs to tweaked.

Declare 
@TableName varchar(300),
@ColumnName varchar(300),
@SQLText varchar(max),
@CharacterMaxLen varchar(max),
@CollationName varchar(max),
@IsNullable varchar(max),
@DataType varchar(max)

SET @CollationName = 'RequiredCollation'

Declare MyTableCursor cursor for
SELECT name FROM sys.tables WHERE [type] = 'U' and name <> 'sysdiagrams' ORDER BY name 
OPEN MyTableCursor

FETCH NEXT FROM MyTableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE MyColumnCursor Cursor
        FOR 
        SELECT COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
            IS_NULLABLE from information_schema.columns
            WHERE table_name = @TableName AND  (Data_Type LIKE '%char%' 
            OR Data_Type LIKE '%text%') AND COLLATION_NAME <> @CollationName
            ORDER BY ordinal_position 
        Open MyColumnCursor

        FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType, 
              @CharacterMaxLen, @IsNullable
        WHILE @@FETCH_STATUS = 0
            BEGIN
            SET @SQLText = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN [' + @ColumnName + '] ' + 
              @DataType + '(' + CASE WHEN @CharacterMaxLen = -1 THEN 'MAX' ELSE @CharacterMaxLen END + 
              ') COLLATE ' + @CollationName + ' ' + 
              CASE WHEN @IsNullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
            PRINT @SQLText 

        FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType, 
              @CharacterMaxLen, @IsNullable
        END
        CLOSE MyColumnCursor
        DEALLOCATE MyColumnCursor
--Print @SQLText
FETCH NEXT FROM MyTableCursor INTO @TableName
END
CLOSE MyTableCursor
DEALLOCATE MyTableCursor

Types of Backups and the background process of database Backup methodology.

Types of Backups and the background process of database Backup methodology.

• Full Backup: It’s just full backup of database, it backs up all data and objects that reside in the database but it will not backup objects like jobs and SQL Server logins which don’t reside in database. With the versions prior to the SQL Server 2005, if you execute a full database backup, you could not make any other backups (such as transactional log backup), until the full database backup is completed. But SQL Server 2005 allows you to do two concurrent backups at the same time. If you exceed two the third backup will fail. It’s advised that not to use more than one backup at a time as it will use high disk I/O. Full backup dose not truncate or backup transactional log. Full backup process:
1. When backup command is executed, SQL server locks the database, blocking all transactions and check points is issued on the database, which writes all deity pages to disk. This ensures that all committed transactions up to the point of backup command will be part of the full backup.
2. SQL Server makes a mark in the transactional log to point where the full back begins. This is important in the recovery process; SQL Server will use this mark to help validate what combination of files can or cannot be used to restore the database from the full backup, releases lock on the database.
3. The data is then read out of the database files to the backup files, which is plain text file that is create on disk or tape.
4. Lock the database, blocking all transactions, and issues checkpoint.
5. When full backup is completed SQL Server makes another mark in the transaction log.
6. Release database lock.
7. Extracts all transactions between the two log marks and append to the backup, by maintaining the consistency of backup for e.g., if someone modifies the data on the pages while backup process, and if he restores the backup he might lose the modified data.
• Differential database backup: A differential database backup contains only the changes made to the database since the last full backup. The main purpose of differential backup is to reduce the number of transactional log backups that needed to be restored at the time of recovery. A differential database backup is possible because SQL Server keeps track of the extents that have changed, in an internal bitmap contained within the data file. When the full backup is made bitmap is reset. One bit is used per extent, which is eight physical contiguous 8kb pages (i.e., extent is 8X8 = 64kb), so single 8kb page can map to about 4GB of data. SQL Server creates one of these mapping pages for every 8,192 extents.
• Transactional log backup: it is a backup of your current transactional log; you can take transactional log back up only if you have taken full backup of the database at least once in its life time. Log backup starts at the log sequence (LSN) number at which previous log backup completed or if it is 1st log backup, it will start at which the full backup was completed. SQL Server then backs up all the subsequent transactions until the backup encounters an open transaction. Once SQL Server encounters the open transaction the log backup completes. Any LSN’s that are backed up will be removed from the transactional log, which enables system to reuse the log space. From recovery standpoint no backup is critical than transactional log backup because it allows you to recover more granular point in time. To apply a transactional log, your database must be restored WITH NORECOVERY or WITH STANDBY. WITH NORECOVERY puts your database in pure loading state where it cannot be used for client connection, but WITH STANDBY puts your database in loading state where clients can access your database in read-only mode, but when a transaction is loaded, the database needs exclusive access to load the log, so it would be unavailable for clients use.
• File and File group backup: File and file group backups are based on the full and differential backups, if your database architecture uses multiple files and file groups, although you can do a standard full or differential backup, you now have the option of doing backups on files or file groups which will make recovery much easier. Although you can do file or file group backup from full or differential backup, the advantage of file or file group backup is when we are doing backup of multi terabyte databases.
• Partial backup: It possible that some of the file groups in SQL Server database as read only and some of them both (read/write). In previous versions of SQL Server, a full backup captures all extents even if the file group is marker as read only, which meant that there were no changes to the data. SQL Server 2005 introduces a new parameter to the backup command to handle this situation. The READ_WRITE_FILEGROUPS clause causes the backup engine to skip any file groups that are marked as read only, saving time and space in the backup by having the backup engine gather only the set of extents that could change. E.g., BACKUP DATABASE PUBS READ_WRITE_FILEGROUPS TO DISK='C:\DEMO\BACKUP\PUBS1.BAK'
• Mirror Backup: this is a new feature from SQL Server 2005 onwards, it is the ability to mirror your backup media.
• Media Retention: It an instance level option where we can set how long SQL Server can retain backup set. Retaining means within SQL Server you cannot delete it (but it will not prevent doing anything physically on your media) this option is media retention and is considered an advanced configuration option within SQL Server. It is set in days, and the values can be from 0 (the default) to 365.

TSQL Script to create Default Database Email

Recently we did a installation of some bulk SQL Server for various purpose. Now we needed database Email to be configured on each server and there had to be some standard. I worked out this my Making a generic script which can be used and run on any machine and which maintain symmetry

USE master
Go

sp_configure 'show advanced options', 1
GO

RECONFIGURE;
GO

sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE
GO

USE msdb
GO

DECLARE
    @profile    VARCHAR(100),
    @display    VARCHAR(100),
    @emailaddr    VARCHAR(100),
    @accountname    Varchar(100)

SELECT
    @profile    = 'MyProfileName_DB_Mail_Profile_' + @@ServerName,
    @display    = 'MyProfileName_DB_Mail_Account_' + @@ServerName,
    @emailaddr    = 'MyRequiredEmailAddress' + @@servername + '@myDomain.com',
    @accountname    = 'MyAlertName_Alert_Account';

-- Create Database Mail account
EXECUTE dbo.sysmail_add_account_sp 
        @account_name = @accountname,
        @description = 'MyDatabaseAccountName Alert Database Account', 
        @email_address = @emailaddr,
        @display_name = @display,
        @mailserver_name = 'MyMailserver.com';        

-- Create a Database Mail profile
EXECUTE dbo.sysmail_add_profile_sp
        @profile_name = @profile,
        @description = 'MyDatabaseEmailProfileName Alert Database Profile';

-- Add multiple Database Mail accounts to Mail profile
EXECUTE dbo.sysmail_add_profileaccount_sp
        @profile_name = @profile,
        @account_name = @accountname,
        @sequence_number = 1;

-- Grant the mail profile access to msdb public role & make it a default
EXECUTE dbo.sysmail_add_principalprofile_sp  
        @profile_name = @profile,
        @principal_name = 'public',
        @is_default = 1;

EXEC master.dbo.xp_instance_regwrite 
        N'HKEY_LOCAL_MACHINE', 
        N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', 
        N'DatabaseMailProfile', 
        REG_SZ, 
        @profile;

GO