How to get user security changes with SQL default trace

Question: There were some recent user security changes on a SQL Server login user account. Is there a way to grab this information - via the default trace? I haven't got a dedicated Extended events monitor set up - so I'm relying on the default trace . Management want to understand the details related to this security change. Answer: It is possible to report on security changes administered on SQL Server via the default trace. In fact - it's excellent - except if the default trace configuration is set up to rollover the files. Recently I had to use the... Read more →

How to get the Active Directory groups membership for user with Powershell Get-ADPrincipalGroupMembership

Question: How can I get the Active Directory groups for a specific user using Powershell? I'm facing a problem where a user is getting a failed login to a SQL Server. This SQL Server has multiple Active Directory groups attached to it - but I don't have visibility to the members of the groups. I know I can use the Get-ADGroupMember powershell cmdlet , which would give the member list of a specified Active Directory group, but this would be time - consuming . Is there a Powershell based method to specify the domain user and return the list of... Read more →

How to list Windows users with Powershell

Question: How can I use Powershell to list out Windows users? Are there Powershell cmdlets which can report on Windows users ? Answer: There are "out of the box" Powershell cmdlets which will support the requirement . How you apply the powershell cmdlets will depend on how much detail is required. Here are some examples: --return local groups Get-localgroup --return all members of a group - in this example the Administrator group Get-LocalGroupMember -Group "Administrators" --Get the local users Get-Local User --SID of a local group (Get-Localgroup Administrators).SID These are some basic examples to extract Windows users information with Powershell.... Read more →

How to troubleshoot SPN and SQL Server Windows return code: 0x2098, state: 15

Question:I see this error in the SQL Server Error Logs . How can I troubleshoot this issue? The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/myserver.net:50234 ] for the SQL Server service.Windows return code: 0x2098, state: 15. The start up account for SQL Server is a dedicated start up account Answer: As a starting point let's look at what MSDN has to say " To use Kerberos authentication with SQL Server requires both the following conditions to be true: The client and server computers must be part of the same Windows domain, or... Read more →

How to check Enforce password expiration is set for SQL Login

Question: We had an unexpected application outage due to a SQL Login which had Enforce password expiration set . Unfortunately this was not being monitored, and led to a situation where the password had expired and the application was able to logon onto the database. It's the sql server team security policy to not have SQL Logins set with MUST_CHANGE ON. Part of enforcing and monitoring the policy includes incorporating certain checks in daily sql server security violations reports. What is the sql code to execute this check and to identify if any SQL Logins have Enforce Password Policy and... Read more →