Vision: SQL Homeostasis

Recently I have had to troubleshoot quite a bit of SQL login issues and often times the issue was with the users active directory user account. I was aware the my organization has security policies that includes among other things, active directory password policy. Of course there is also SQL Server user security policy, which I know by heart. To better support my users, I thought it would be a good idea to familiarize myself with the active directory password policy that is in effect. Now, there is a document that outlines the policy but, I want to get what is actually implemented. Here, I am using powershell to get the password policy values. You will need to have the ActiveDirectory PowerShell module installed for the following cmdlets to work. Get-ADUserResultantPasswordPolicy -Identity aduser1 That returned nothing in my case. That most likely implies that the password policy is not assigned per user basis. So now I am going to check what is the default password policy in the...

If your organization uses a password policy (there are very good odds these days that it does) and, especially stricter password requirement for administrative users, your might have experienced instances where yours or your users Active Directory user might be locked out. How do you check if that is the case? Well, for one thing the Windows will tell you so when you try to login and/or failed login attempts are logged in to sql log, event logs etc.  What if user does not logout or have more than one user account, one for regular use and one for administrative tasks? There maybe other scenarios where you have a need to check status of a user account in the Active Directory. I don't have admin privileges and presumably you don't either.  However, I do have read permission on the AD so I could have used Active Directory Users and Groups snap-in. But, here I am going to show the powershell way. You will need to have the ActiveDirectory PowerShell module installed for the fol...

I should start off by mentioning that this post is applicable to sql server versions 2012 and up. If you have an older version of sql server, the solution discussed here will not work. We are generally aware that a user in a database is orphaned when it does not have a matching SID record in the sys.server_principals table.   This is not an issue if your databases is CONTAINED and uses database authentication. Otherwise, the user will not be able to login into the sql server instance and as a result cannot access the database even though the user has access to the database. Generally, you will get orphaned database users after restoring a database to a different server and one or more users in the database do not have corresponding LOGIN at the instance level or has mismatched SID.  Another possibility is that the login got deleted from sys.server_principals or from the Active Directory or local machine. I am sure there are other possible situations. Microsoft h...