February 28, 2019 at 1:51 pm
Our DBA left and they shut off his account. Of course, things started failing all over the place so I was tasked to help. I'm checking the service accounts and job owners. Any advice on other places I should be looking? Thanks, as always, for any direction or help.--show current server
select @@SERVERNAME
--check the service accounts
SELECT servicename, service_account
FROM sys.dm_server_services
GO
--check for failed jobs
use msdb
go
select h.server as [Server],
j.[name] as [Name],
h.message as [Message],
h.run_date as LastRunDate,
h.run_time as LastRunTime
from sysjobhistory h
inner join sysjobs j on h.job_id = j.job_id
where j.enabled = 1
and h.instance_id in
(select max(h.instance_id)
from sysjobhistory h group by (h.job_id))
and h.run_status = 0
--check job owners
select s.name,l.name
from msdb..sysjobs s
left join master.sys.syslogins l on s.owner_sid = l.sid
where l.name <> 'sa'
February 28, 2019 at 1:54 pm
If he was running things with his own account you're probably better off.....
February 28, 2019 at 2:00 pm
here's a snippet which checks for database owners and job owners: you will need to run this on each server:
DECLARE @name VARCHAR(128)
SELECT @name = name FROM sys.server_principals WHERE sid = 0x01
--check all databases
select
'Database Not owned by ' + @name + ';' As DB_Issues,
name,
suser_sname(dbz.owner_sid) As TheOwner ,
CASE
WHEN suser_sname(dbz.owner_sid) <> @name
THEN 'ALTER AUTHORIZATION ON DATABASE::' + name + ' to ' + QUOTENAME(@name) + ';'
ELSE ''
END As cmd
from sys.databases dbz
WHERE suser_sname(dbz.owner_sid) <> @name
order by dbz.name;
select 'Job Not Owned By '+ @name + ';' AS Jobs_Issue,
'msdb' As DatabaseName,
jb.name As JobName,
CONVERT(VARCHAR(128),SUSER_SNAME(jb.owner_sid)) As TheOwner,
'EXEC msdb.dbo.sp_update_job @job_name=N''' + jb.name + ''', @owner_login_name=N''' + @name + '''' AS cmd
from msdb.dbo.sysjobs jb
LEFT JOIN master.sys.server_principals lg
ON jb.owner_sid = lg.sid
where CONVERT(VARCHAR(128),SUSER_SNAME(jb.owner_sid)) <> @name OR CONVERT(VARCHAR(128),SUSER_SNAME(jb.owner_sid)) IS NULL
Lowell
February 28, 2019 at 2:15 pm
Thanks a bunch! Will him being the owner of the database cause issues? I mean, it makes sense that it would but I'm just prioritizing addressing the issues at the present.
February 28, 2019 at 3:19 pm
There can be flaky issues if you lose an account that owns things. It's worth doing some cleanup, and saving these scripts. You'll use them again.
I'd change the owner of everything this person had. Jobs and databases, but check for anything inside databases. Depending on how the account was there, you might have something that's owned by an orphan.
February 28, 2019 at 4:14 pm
one other possible issue - if the instances were running under his ID, any backup location may also require permissions to be changed.
This both for doing backups and to restore (possibly from other servers!!)
March 1, 2019 at 9:54 am
frederico_fonseca - Thursday, February 28, 2019 4:14 PMone other possible issue - if the instances were running under his ID, any backup location may also require permissions to be changed.
This both for doing backups and to restore (possibly from other servers!!)
Similarly anything else that's accessing the filesystem so SSIS tasks would be the primary concern. Those locations will need equivalent permissions adding for the service account you get set up to replace the user's account.
Hopefully you won't have any Windows scheduled tasks to worry about but if the owner of those is deleted they will fail and you won't be able to edit them even if they were set up to run under a different user. They will need recreating from scratch - if its SQL related take the opportunity to move it to being a SQL job instead.
Now is an excellent time to fix the way access is being granted
Ultimately anything that needs to continue to run after the person leaves should be set to run under a SQL Role or a Windows AD group and if this is not possible use service accounts rather than user accounts.
Doing so makes it vastly simpler to manage access to systems as you generally just need to add or delete members from roles or groups (and you can nest them) when that persons job role changes or they leave the company.
March 1, 2019 at 10:30 am
DataAnalyst011 - Thursday, February 28, 2019 1:51 PMOur DBA left and they shut off his account. Of course, things started failing all over the place so I was tasked to help. I'm checking the service accounts and job owners. Any advice on other places I should be looking? Thanks, as always, for any direction or help.--show current server
select @@SERVERNAME--check the service accounts
SELECT servicename, service_account
FROM sys.dm_server_services
GO--check for failed jobs
use msdb
go
select h.server as [Server],
j.[name] as [Name],
h.message as [Message],
h.run_date as LastRunDate,
h.run_time as LastRunTime
from sysjobhistory h
inner join sysjobs j on h.job_id = j.job_id
where j.enabled = 1
and h.instance_id in
(select max(h.instance_id)
from sysjobhistory h group by (h.job_id))
and h.run_status = 0--check job owners
select s.name,l.name
from msdb..sysjobs s
left join master.sys.syslogins l on s.owner_sid = l.sid
where l.name <> 'sa'
Of utmost importance, make sure that the "acting" DBA (and someone else) has sys_admin privs. Change the "SA" password (and store it in a safe place like someone's physical safe) and then make sure that the "SA" account is disabled.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 1, 2019 at 11:29 am
You all are a life-saver. Thank you so much for all the advice. I've switch most components to dedicated system accounts and generally tightening security.
I'm going to start working through the sa passwords. Unfortunately, in some instances the password to any sa level accounts has been completely lost. After researching, it looks like the procedure here is my only option (unless anyone else has advice/direction):
March 1, 2019 at 2:02 pm
DataAnalyst011 - Friday, March 1, 2019 11:29 AMYou all are a life-saver. Thank you so much for all the advice. I've switch most components to dedicated system accounts and generally tightening security.I'm going to start working through the sa passwords. Unfortunately, in some instances the password to any sa level accounts has been completely lost. After researching, it looks like the procedure here is my only option (unless anyone else has advice/direction):
You should also run an inventory of all your SQL Servers, SSAS, SSIS and SSRS with Microsoft Assessment and Planning Toolkit (MAP) on your network.
https://www.microsoft.com/en-us/download/details.aspx?id=7826
March 1, 2019 at 5:15 pm
DataAnalyst011 - Friday, March 1, 2019 11:29 AMYou all are a life-saver. Thank you so much for all the advice. I've switch most components to dedicated system accounts and generally tightening security.I'm going to start working through the sa passwords. Unfortunately, in some instances the password to any sa level accounts has been completely lost. After researching, it looks like the procedure here is my only option (unless anyone else has advice/direction):
Don't you have sys_admin privs on all the boxes? If so, then you have privs to change the password for the "SA" user. Of course, you'll then need to "keep your ear on the rail" for any apps where folks have made the mistake of using the "SA" login for the app.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2019 at 7:48 am
Jeff Moden - Friday, March 1, 2019 5:15 PMDataAnalyst011 - Friday, March 1, 2019 11:29 AMYou all are a life-saver. Thank you so much for all the advice. I've switch most components to dedicated system accounts and generally tightening security.I'm going to start working through the sa passwords. Unfortunately, in some instances the password to any sa level accounts has been completely lost. After researching, it looks like the procedure here is my only option (unless anyone else has advice/direction):
Don't you have sys_admin privs on all the boxes? If so, then you have privs to change the password for the "SA" user. Of course, you'll then need to "keep your ear on the rail" for any apps where folks have made the mistake of using the "SA" login for the app.
Thanks a bunch. In most cases, yes. That's a good idea and I'll monitor to the logs for failed sa attempts. In other cases, it looks like they missed adding the AD Group that also has sys admin privileges.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply