A very interesting paragraph from a Microsoft KB article about NT AUTHORITY\SYSTEM and BUILTIN\Administrators logins

The original article is here and the quoted text is as follows:

The NT AUTHORITY\SYSTEM account
The NT AUTHORITY\SYSTEM account is also granted a SQL Server login. The NT AUTHORITY\SYSTEM account is provisioned in the SYSADMIN fixed server role. Do not delete this account or remove it from the SYSADMIN fixed server role. The NTAUTHORITY\SYSTEM account is used by Microsoft Update and by Microsoft SMS to apply service packs and hotfixes to a SQL Server 2005 installation. The NTAUTHORITY\SYSTEM account is also used by the SQL Writer Service.

Also, if SQL Server 2005 is started in single-user mode, any user who has membership in the BUILTIN\Administrators group can connect to SQL Server 2005 as a SQL Server administrator. The user can connect regardless of whether the BUILTIN\Administrators group has been granted a server login that is provisioned in the SYSADMIN fixed server role. This behavior is by design. This behavior is intended to be used for data recovery scenarios.

As for the argument whether or not to delete login “BUILTIN\Administrators”, this article basically anwsers it all. Since all local administrators can still access database engine during single user mode which only occurs during disaster recovery situations, it is very safe to remove “BUILTIN\Administrators” to reduce hackers’ attack vector. However, the second bold part sounds a little bit ambiguous and I am planning to test it out myself on a text box soon.

Update on April 13, 2011

I just carried a test about the default Built-in administrators’ ability to log on to SQL Server as sysadmin when the SQL Server servcie is running under single user mode. And the test result is positive. You actually can delete the login “BUILTIN\Administrtors” and still use a local admin account to log on to SQL Server as a sysadmin under single user mode. Once the SQL Server service is backup to multiple user mode, the local admin account failed to log in.

Therefore, to have a super compliant and secure management on SQL Server sysadmin accounts, the best thing to do is to put all user accounts in an AD group and assign this group as sysadmin role and delete BUILTIN\Administrators from the list of logins in SQL Server.

Script to return the service accounts of sql server and sql agent services


/* This script reads out the names of sql server service account and sql server agent service account
Author:Ning Xu Date: 2010-09-16 Applied version: 2000, 2005, 2008 */

DECLARE @serviceaccount as varchar(50)
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER', N'ObjectName', @serviceAccount OUTPUT, N'no_output'

SELECT @Serviceaccount as SQLServer_ServiceAccount

EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT', N'ObjectName', @serviceAccount OUTPUT, N'no_output'

SELECT @serviceaccount as SQLAgent_ServiceAccount
go


How to become NT AUTHORITY\SYSTEM on Windows systems

As a DBA, you will bump into situations to trouble shoot weird things under this account. And this article details the way how to do it beautifully. Just before you follow the steps, you have to install a toolset from Microsoft to run all the commands that are needed in that tutorial. Also don’t forget to delete the temporary Windows job that you’ve created just for this occassion.

Script to backup all databases in an instance


/*
This script make full backups of all user databases and three system databases in a specific instance.
All backup files will named as "db_name_current_date.bak".
Watch out that you have to set up the correct backup directory in the @path parameter.
Author: Ning Xu
Date: 22-09-2010
Applied version: 2000, 2005, 2008, 2008 R2
*/

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

SET @path = 'C:\Temp\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name not IN ('tempdb')

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

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor


Remove full duplicates by using row_number() function and common table expression

This blog article is inspired by another very informative one which explains really well the meaning of the new row_number() function introduced since SQL2005. Since the original article did not give a full answer to how to delete multiple fully duplicated rows in a table, I am putting my code here for the test.

The good thing about row_number() function is that it can be based on partition of columns. New row number count starts for each partition of full duplicates and those rows with row number bigger than 1 can be easily spotted and removed. Here is the example that explains everything I am talking about here.

First of all, create a table in a test db

USE [test_db]
GO
CREATE TABLE [dbo].[Employee](
[EMPID] [int] NULL,
[FNAME] [varchar](50) NULL,
[LNAME] [varchar](50) NULL
) ON [PRIMARY]

Then let’s insert many duplicated rows:


INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2021110, 'MICHAEL', 'POLAND')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2021110, 'MICHAEL', 'POLAND')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2021115, 'JIM', 'KENNEDY')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2021115, 'JIM', 'KENNEDY')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2021115, 'JIM', 'KENNEDY')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2121000, 'JAMES', 'SMITH')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2121000, 'JAMES', 'SMITH')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2011111, 'ADAM', 'ACKERMAN')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (3015670, 'MARTHA', 'LEDERER')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (3015670, 'MARTHA', 'LEDERER')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (3015670, 'MARTHA', 'LEDERER')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (3015670, 'MARTHA', 'LEDERER')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (1021710, 'MARIAH', 'MANDEZ')
GO


If you selete everything from this table now, the following should be there:

EMPID	FNAME	LNAME
1021710	MARIAH	MANDEZ
2011111	ADAM	ACHERMAN
2021110	MICHAEL	POLAND
2021110	MICHAEL	POLAND
2021115	JIM	KENNEDY
2021115	JIM	KENNEDY
2021115	JIM	KENNEDY
2121000	JAMES	SMITH
2121000	JAMES	SMITH
3015670	MARTHA	LEDERER
3015670	MARTHA	LEDERER
3015670	MARTHA	LEDERER
3015670	MARTHA	LEDERER

And here comes the de-dup script with the help of common table expression:


WITH temp_employee AS
(SELECT ROW_NUMBER() OVER (PARTITION BY EMPID, FNAME, LNAME ORDER BY EMPID) AS rowid, * FROM EMPLOYEE)
DELETE temp_employee WHERE rowid > 1


Hope this article is illustrative enough.

Script that fixes the “sql server agent service won’t start” problem


/*
This script solves the "sql server angent service won't start" problem.

Usually if you are pretty sure that the service account running sqlserveragent has sufficient rights and this service still refuses to start up, it is most of the time becuase of either the misconfigured "Agent XPs" or inaccessible sql server agent errorlog file. The following script fixes both issues.

Note: change the followng path for errorlog to a correct path according to your local server's setting.

Author: Ning Xu
Date: 2011-02-17
Applied version: 2005, 2008, 2008 R2
*/

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

sp_configure 'Agent XPs', 1;
GO
RECONFIGURE with override
GO

USE msdb
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N'D:\Programs\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT'
GO

Shortcuts to Microsoft MMC snap-in’s

Certificates	certmgr.msc
Indexing Service	ciadv.msc
Computer Management	compmgmt.msc
Device Manager	devmgmt.msc
Disk Defragmenter	dfrg.msc
Disk Management	diskmgmt.msc
Event Viewer	eventvwr.msc
Shared Folders	fsmgmt.msc
Group Policy	gpedit.msc
Local Users and Groups	lusrmgr.msc
Removable Storage	ntmsmgr.msc
Removable Storage OperatorRequests	ntmsoprq.msc
Performance	perfmon.msc
Resultant Set of Policy	rsop.msc
Local Security Settings	secpol.msc
Services	services.msc
Windows Management Infrastructure (WMI)	wmimgmt.msc
Component Services	comexp.msc
IIS Manager	inetmgr
SQL Server Configuration Manager	sqlservermanager.msc(for version 2005)sqlservermanager10.msc(for version 2008, 2008 R2)