SQL Server security monitoring is a critical part of the Database Administrator’s job. Some security related alerts could be setup very easy, but others require third-party tools or extra steps to setup. For example, SQL Server Audit can be used to monitor logins or users modification, but it requires audit log review. We would like to get real-time alerts every time a login or a user is created or added to a server or a database role, how can this be done?
In this tip we will provide steps and scripts for setting up WMI alerts and jobs responding to these alerts to monitor the creation and removal of users and logins as well as server and database roles membership changes.
During our regular security reviews we have discovered that one of the SQL Servers with Availability Groups has an individual DBA’s login in addition to the DBA group as a member of the sysadmin server role. The DBA is already a member of the DBA Domain group, so we are not sure why we had this login on SQL Server.
We have tried to remove the login, but couldn’t remove it.
In this tip we will be providing troubleshooting steps required for the Drop Login issues.