quote:

---

Re-working the BUILTIN\Administrator SQLServer Login Account

This is well described in Brian Knight’s article “Removing NT Administrator as Sysadmins”. On installation of SQL Server a login account called “BUILTIN\Administrators” is created, anyone allocated to the Administrators NT local group on the server will have sysadmin access to the database server via natural Windows Authentication. This login will give the user DBO access to all system and user databases coupled with sysadmin access.

---

Chris,

As the "First" DBA in our shop, I have found many things that need to be fixed in this area. I have also had to work with our net admins using this to obatain access to systems that had been configured by persons no longer employed or contracted by our company.

We also, for some legacy reasons, have some domain admin accounts that have had their security compromised, (Some "white hats" came in, hoping to sell services, and showed that they could "crack" passwords. Why they had to put the complete password in the clear for all accounts I'll never know, but the FUD *was* effective, but the whole thing is another story..) and management hasn't decided on how to deal with it. In that case, it the SQL server is one that might be of interest to the people in our organization who were wrting all those passwords down, I have a QA shell that I run, filling in the appropriate domain\user that I wish to remove:

-- Deny_to_NTUser.sql Version 1.0, Release 1.0

-- Purpose: Denies NT User Rights

-- Hal Smith 2001-08-28 12:01:01.593

-- =============================================

-- setup

set nocount on

select getdate(), @@servername

go

-- execute

-- =============================================

-- Drop login from server role

-- =============================================

sp_dropsrvrolemember @loginame = N'<Windows_or_Sql_Server_login, sysname, REDMOND\john>', @rolename = N'<server_role, sysname, sysadmin>'

GO

-- =============================================

-- Deny Sql Server access to Windows user or group

-- =============================================

sp_denylogin @loginame = N'<Windows_user_or_group, sysname, REDMOND\john>'

GO

-- /\/\/\/\/-- Data Follows

/* I clip the queries' ouput out of the results window, and paste it in here. Then I save the whole query to the security matrix documentation for the particular server. I always try to script *all* actions like this.

-- Data Preceeds

-- /\/\/\/\/

-- cleanup

select getdate(), @@servername

set nocount off

go

-- End of Deny_to_NTUser.sql Version 1.0, Release 1.0

I do like your idea for builtin\administrator rework, and plan to do that next server visits scheduled with the net admins.

-hal