Sql-server – Status of objects owned & permissions granted by a (now)-disabled Windows Account

permissionsSecuritysql server

When an employee (esp. sysadmin) leaves (ie, their AD account is disabled), what happens to objects they 'owned' (permissions they granted) in SQL Server?

In other words, for SQL Server security, do I have to change the ownership of those objects, grants?

(Currently on SQL Server 2008; anticipating upgrade to 2016 this fall.)

Best Answer

You'll want to change ownership of databases that are owned by the disabled user. I'd recommend creating a dedicated account, either SQL Server authentication, or Windows Auth if you're not using SQL auth, to own each individual database.

You can identify databases that are owned by the individual in question using this query:

SELECT DatabaseName = d.name
    , PrincipalName = sp.name
FROM sys.databases d
    INNER JOIN sys.server_principals sp ON d.owner_sid = sp.sid
WHERE sp.name = '<DOMAIN\USER>';

Modify ownership on a database using ALTER AUTHORIZATION like this:

ALTER AUTHORIZATION ON DATABASE::<database_name_here>
TO <new_owner_name_here>;

Any SQL Server agent jobs that are configured to run "as" the disabled user will not run.

You can identify SQL Server Agent Job Owners using this query:

SELECT JobName = sj.name
    , OwnerName = sp.name
FROM msdb.dbo.sysjobs sj
    INNER JOIN sys.server_principals sp ON sj.owner_sid = sp.sid
WHERE sp.name = '<DOMAIN\USER>';

Change the job owner using this T-SQL:

EXEC msdb.dbo.sp_update_job @job_name = '<job_name_here>'
    , @owner_login_name = '<new_owner_name_here>';

Any stored procedures or other objects that are owned by the disabled account, where no one else has rights will obviously be problematic.

Related Question