以前、SQL Server エージェントで Transact-SQL を実行する際に使用されるログインについて という投稿を書きました。
この投稿の中では、sysadmin ロールに含まれていないログインをジョブの所有者に設定した場合の挙動に触れてみました。
ジョブの所有者が sysadmin ロール以外で、Windows 認証のログインを使用している場合、OS 側のログインが無効の状態になっている場合、どのような挙動となるかについて確認する必要がありましたので、その内容をまとめておきたいと思います。
ローカルアカウントではなく、ドメインアカウントを対象としたものについては、次の情報を確認してください。
- What happens if agent job owner account is deactivated in Active directory?
- Who owns your jobs?
- Jobs Owned by User Accounts
Contents
SQL Server エージェントのジョブ実行時に使用されるログインの基本的な考え方
SQL Server エージェントのジョブで Transact SQL を実行する場合、最初の動作については、「SQL Server エージェントサービスのサービスアカウント」で実行が行われています。
その後、ジョブステップが実行されますが、ジョブの所有者が sysadmin ロールのログインの場合は、そのまま SQL Server エージェントサービスのサービスアカウントで実行されます。
sysadmin ロール以外の場合は、「EXECUTE AS LOGIN = ‘<所有者のログイン名>’ WITH NO REVERT」で、所有者のログイン情報に切り替えてジョブステップ内のクエリが実行されるという挙動となります。
ジョブ所有者のアカウント情報の存在確認
ドキュメントに記載されているものは見つからず、私が動作確認をした範囲で判断したものとなりますが、sysadmin 以外のログインでジョブステップを実行した場合、所有者のション存在確認は次のようになっているように見受けられました。
- SID を使用して Windows アカウントが OS 上に存在しているかを確認
- ログイン名を使用して、Windows アカウントが OS 上に存在しているかを確認
Windows アカウントが存在しているかの確認のみ実施しているようでした。
- OS 上でアカウントが無効
- SQL Server と OS の SID が異なっている
- パスワードの変更が必要というような
状態となっていても、「EXECUTE AS LOGIN」でスイッチするアカウントとしても有効となっているようです。
アカウントを削除してしまうと上記の確認で存在していないとして認識されてしまうのですが、同一名称のアカウントが存在していれば、SID が異なっていても有効なアカウントとして認識されていました。
一度ログオンが成功すると、キャッシュされているような動作も行われているようにも見え、SQL Server や OS の再起動 / ある程度の時間が経過した場合は、異なる SID については挙動が変わる可能性もあるかもしれませんが、無効になっているアカウントについては、無効状態は影響せずに「EXECUTE AS LOGIN」を実行することができるようでした。
SQL Server ログインが存在しない Windows アカウントで EXECUTE AS LOGIN
Windows 認証を実行する場合、SQL Server 上に Windows 認証用のログインを作成して、クエリを実行することになるかと思います。
それでは、SQL Server 上に Windows 認証用のログインを作成していない Windows アカウントで EXECUTE AS LOGIN を実行するとどうなるでしょうか。
結論としては、Windows 認証用のログインが存在しない Windows アカウントで EXECUTE AS LOGIN を実行しても、システムデータベースであればクエリの実行はできるようです。
これは、ユーザー データベースに対する guest の権限 の記載が影響しているようなのですが、システムデータベースについては guest ユーザーが有効になっています。
そのため、「Windows 認証ログインが存在していない Windows アカウント」に EXECUTE AS LOGIN でスイッチした場合、ユーザーデータベースであればアクセスが拒否されるのですが、システムデータベースについては、guest が有効になっているため、ログインのスイッチを実行することができ「guest ユーザー」としてクエリの実行を行うことができるようです。
ジョブの所有者を変更する際には、無効になっている sa を設定するという方法が使用されることがありますが、SQL Server エージェントのジョブについては、所有者に設定しているアカウントの有効 / 無効の状態はあまり重要視されていないのかもしれませんね。