July 19, 2012 at 11:09 am
We have a few sysadmins who have both pure sql logins and domain logins. So, its time to remove the builtin\administrators. There is only one network admin who I have added individually as a sysadmin because he will be running the Netapp SQL Snap Manager software when I'm not available ( to configure backups, handle restores, etc )
The safest way is probably first remove that group from sysadmin, and observe for a few days. Then disable the login before eventually removing.
Thoughts?
July 19, 2012 at 11:18 am
Have you made sure SQL Server, SQL Agent, et al, are all running/starting under some other credential and won't lose access?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 19, 2012 at 11:28 am
Yes those run under domain service accounts e.g. CM\sql_service
July 19, 2012 at 12:28 pm
Authorization on those by account or by group?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 19, 2012 at 1:08 pm
The individual domain service account is directly a sql sysadmin, and also a sysadmin via a group called CM\Production Service Accounts.
I'm using this to check on ownership by builtin\administrators and not finding any results:
declare @login varchar(25)
set @login='builtin\Administrators'
declare @sql varchar(MAX), @DB_Objects varchar(512)
Select @DB_Objects = ' L.name as Login, U.Name as [User], O.*
From %D%.sys.objects o
Join %D%.sys.database_principals u
ON Coalesce(o.principal_id, (Select S.Principal_ID from %D%.sys.schemas S Where S.Schema_ID = O.schema_id))
= U.principal_id
left join %D%.sys.server_principals L on L.sid = u.sid'
Select @sql = 'SELECT * FROM
(Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '
+ Replace(@DB_objects, '%D%', [name])
From master.sys.databases
Where [name] = 'master'
Select @sql = @sql + ' UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', '
+ Replace(@DB_objects, '%D%', [name])
From master.sys.databases
Where [name] != 'master'
Select @sql = @sql + ') oo Where Login = ''' + @login + ''''
--print @sql
EXEC (@sql)
July 19, 2012 at 4:09 pm
I went ahead and removed builtin admins from sysadmin and found a monitoring account couldn't login. Gave that its own login to this sql server and then was able to leave builtin admins out of sysadmin. I'll disable builtIns after a few days.
July 20, 2012 at 1:11 am
Since this is SQL server 2005 and theres no mention of clustering, the service account will already be a member of the SQL server local groups on the machine and these groups are provisioned by default with sysadmin. Removing built in admins should pose no issue in thi scenario?
You'll also want to lockdown the local administrators group on the server as membership here provides users who know what they're doing the ability to compromise SQL server security. A group policy would be the best way forward to manage this local group.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply