Adding a User to Multiple Databases

Everyone seems to have their own version. Mine is as simple as I could make it. Make sure you create the LOGIN first, and set the @User variable in the code below.

Assumes you have already created a login named @User . 
set nocount on

declare @User sysname
set @User = 'trespasserswill'                  -- Change this!! 

declare @Sql nvarchar(4000)

set @Sql =
'
    use [?]

    if db_id() > 4
    begin
        print ''?''
        create user ' + quotename(@User) + ' for login ' + quotename(@User) + '
        exec sp_addrolemember ''db_datareader'', ''' + @user + '''
        exec sp_addrolemember ''db_datawriter'', ''' + @user + '''
    end
'

exec sp_MSForEachDB @Sql

Display All Databases Using FULL Recovery Model, Yet Having No Log Backups

If a database is using the FULL Recovery Model, it obviously needs to be doing log backups - otherwise the log will eventually fill the disk (your "eventually" may vary). If you don't need log backups, change the database to SIMPLE Recovery Model and sleep easier at night. But, you know how it is - one way or another, databases wind up in this mongrel state. Run this script on your oldest server - you'll probably be surprised.

This script also prints out the T-SQL command to set each database to SIMPLE Recovery Model, so you can just copy and paste.

-- Find databases in FULL Recovery Model with no log backups. 
   SELECT d.name                                            AS 'DatabaseName'
        , MAX(db.recovery_model_desc)                       AS 'Model'
        , MAX(ISNULL(b.backup_finish_date, '1900-01-01'))   AS 'LogBackupFinishDate'
        , 'ALTER DATABASE ' + QUOTENAME(d.name) 
        + '  SET RECOVERY SIMPLE WITH NO_WAIT'              AS 'SqlStatement'
     FROM sys.sysdatabases   d
     JOIN sys.databases      db
       ON d.dbid = db.database_id
LEFT JOIN msdb..backupset    b
       ON b.database_name = d.name
      AND b.type          = 'L'
    WHERE db.recovery_model_desc = 'FULL'
      AND db.state_desc          = 'ONLINE'
      AND db.database_id         > 4
      AND db.is_read_only        = 0
 GROUP BY d.name
   HAVING MAX(ISNULL(b.backup_finish_date, '1900-01-01')) < DATEADD(Day, -180, GETDATE())
 ORDER BY d.name

Script to verify if "Instant File Initialization" is Enabled

Instant file initialization is a wonderful feature of the Windows file system - not of SQL Server, please note -- that allows the server to allocate disk pages without having to set each bit to zero. You can imagine the time savings when SQL Server is, say, adding a GB to a data file. Note that because of the way they embedded information in the first few bytes of the actual pages of the log files themselves - sigh - log files can't use Instant File Initialization. But still, it's a must-have (unless the idea of having old data laying around in your pages gives your security people the willies - best check with them). You can find lots of info about this all over the web, but here's a little script that will verify that you've jumped through all the hoops correctly.

--  Once you have added the SQL Server security account to this security policy 
--  you need to then execute gpupdate /force from the command prompt and restart 
--  the SQL Service for this change to take effect.

USE master
SET NOCOUNT ON

-- If a database named DummyTestDB_VerifyIFI already exists drop it. 
IF DB_ID('DummyTestDB_VerifyIFI') IS NOT NULL
    DROP DATABASE DummyTestDB_VerifyIFI

-- Temp table to hold output from sp_readerrorlog. 
IF OBJECT_ID('tempdb..#SqlLogs') IS NOT NULL 
    DROP TABLE #SqlLogs

GO

CREATE TABLE #SqlLogs(LogDate datetime, ProcessInfo VARCHAR(20), TEXT VARCHAR(MAX))

-- Turn on trace flags 3004 and 3605. 
DBCC TRACEON(3004, 3605, -1) WITH NO_INFOMSGS

-- Create a dummy database to see the output in the SQL Server Errorlog. 
CREATE DATABASE DummyTestDB_VerifyIFI 
GO

-- Turn off trace flags 3004 and 3605. 
DBCC TRACEOFF(3004, 3605, -1) WITH NO_INFOMSGS

-- Remove the dummy database. 
DROP DATABASE DummyTestDB_VerifyIFI

-- Now go check the output in the current SQL Server errorlog File. 
-- This can take a while if you have a large errorlog file. 
INSERT INTO #SqlLogs(LogDate, ProcessInfo, TEXT)
    EXEC sp_readerrorlog 0, 1, 'Zeroing'

IF EXISTS (SELECT * FROM #SqlLogs
            WHERE TEXT LIKE 'Zeroing completed%'
              AND TEXT LIKE '%DummyTestDB_VerifyIFI.mdf%'
              AND LogDate > DATEADD(HOUR, -1, LogDate))
    PRINT 'We do NOT have instant file initialization.'
ELSE
    PRINT 'We have instant file initialization.'

Rename SQL Agent Operator and all its Notifications

This script will change an Operator's name and update all three notification option types the Operator has.

-- Will change from the old Operator to the new Operator for all 
-- SQL Agent Alerts and Jobs for all three notification options. 
use msdb
set xact_abort on

-- Set these variables to the old and new Operator of your choice. 
-- This is the only code you have to change. 
declare @OperatorNameOld sysname
declare @OperatorNameNew sysname
set @OperatorNameOld = 'My Old Operator Name'
set @OperatorNameNew = 'My New Operator Name'

-- Verify your old operator exists. 
if not exists (select * from sysoperators where name = @OperatorNameOld)
    raiserror('   *** @OperatorNameOld ''%s'' does not exist!', 20, 1, @OperatorNameOld) with nowait, log

print 'Old operator name is ''' + @OperatorNameOld + ''' '

-- Verify your new operator exists. 
if not exists (select * from sysoperators where name = @OperatorNameNew)
    raiserror('   *** @OperatorNameNew ''%s'' does not exist!', 20, 1, @OperatorNameNew) with nowait, log

print 'New operator name is ''' + @OperatorNameNew + ''' '

-- Iterate through the Alerts and change the operator. 
declare @AlertNameVar            sysname
declare @IncludeEventDescription tinyint

declare cur cursor for
    select sa.name                        as AlertName,
           so.name                        as OperatorNameOld,
           sa.include_event_description   as IncludeEventDescription
      from sysalerts          sa
      join sysnotifications   sn
        on sa.id = sn.alert_id
      join sysoperators       so
        on sn.operator_id = so.id
     where so.name = @OperatorNameOld

open cur
fetch next from cur into @AlertNameVar, @OperatorNameOld, @IncludeEventDescription

while @@fetch_status = 0
begin
    print 'Alert ''' + @AlertNameVar + ''' - adding new operator ''' + @OperatorNameNew + ''' '

    exec sp_add_notification
        @alert_name          = @AlertNameVar,
        @operator_name       = @OperatorNameNew,
        @notification_method = @IncludeEventDescription

    print 'Alert ''' + @AlertNameVar + ''' - deleting old operator ''' + @OperatorNameOld + ''' '

    exec sp_delete_notification
        @alert_name    = @AlertNameVar,
        @operator_name = @OperatorNameOld

    fetch next from cur into @AlertNameVar, @OperatorNameOld, @IncludeEventDescription
end

close cur
deallocate cur

-- Iterate through the Jobs and change the operator 
-- for each of the three kinds of notifications. 
declare @JobNameVar             sysname
declare @OperatorNameMailOld    sysname
declare @OperatorNamePageOld    sysname
declare @OperatorNameNetSendOld sysname

declare cur cursor for
    select sj.name          as JobName,
           soMail.name      as OperatorNameMailOld,
           soPage.name      as OperatorNamePageOld,
           soNetSend.name   as OperatorNameNetSendOld
      from sysjobs        sj
 left join sysoperators   soMail
        on sj.notify_email_operator_id = soMail.id
 left join sysoperators   soPage
        on sj.notify_page_operator_id = soPage.id
 left join sysoperators   soNetSend
        on sj.notify_netsend_operator_id = soNetSend.id
     where (soMail.name    = @OperatorNameOld
        or  soPage.name    = @OperatorNameOld
        or  soNetSend.name = @OperatorNameOld)

open cur
fetch next from cur into @JobNameVar, @OperatorNameMailOld, @OperatorNamePageOld, @OperatorNameNetSendOld

while @@fetch_status = 0
begin
    -- Mail Job notification. 
    if @OperatorNameMailOld is not null
    begin
        print 'Job ''' + @JobNameVar + ''' - changing Mail operator name to ''' + @OperatorNameNew + ''' '

        exec msdb.dbo.sp_update_job 
            @job_name                   = @JobNameVar, 
            @notify_email_operator_name = @OperatorNameNew
    end

    -- Page Job notification. 
    if @OperatorNamePageOld is not null
    begin
        print 'Job ''' + @JobNameVar + ''' - changing Page operator name to ''' + @OperatorNameNew + ''' '

        exec msdb.dbo.sp_update_job 
            @job_name                  = @JobNameVar, 
            @notify_page_operator_name = @OperatorNameNew
    end

    -- NetSend Job notification. 
    if @OperatorNameNetSendOld is not null
    begin
        print 'Job ''' + @JobNameVar + ''' - changing NetSend operator name to ''' + @OperatorNameNew + ''' '

        exec msdb.dbo.sp_update_job 
            @job_name                     = @JobNameVar, 
            @notify_netsend_operator_name = @OperatorNameNew
    end

    fetch next from cur into @JobNameVar, @OperatorNameMailOld, @OperatorNamePageOld, @OperatorNameNetSendOld
end

close cur
deallocate cur

-- Delete old operator. 
exec msdb.dbo.sp_delete_operator @name = @OperatorNameOld
print 'Dropped old operator name ''' + @OperatorNameOld + ''' '

go

How to Find Operators with a Specific Email Address

This is one of those that I can never remember, for some reason. This is very handy in CMS, where you can search all your servers at once.

-- Find Operator for email address. 
  select distinct o.name     as 'Operator Name'
       , o.email_address     as 'Email Address'
    from msdb..sysoperators   o
   where @@servername not like 'BAD_SERVERS%'
     and o.email_address = 'MyEmailAddress@mydomain.com'
order by o.name
       , o.email_address