How to extract Active Directory users with Get-ADGroup and Get-ADGroupMember

Question: I have a large list of Active Directory Groups and would like to extract the users from these Active Directory Groups. How can I script to iterate through a large list of Active Directory Group and list users using Powershell? Answer: Powershell has a very effective set of Active Directory modules. Within the set of AD modules , it is possible to achieve listing AD group members , group per group , combining Powershell Get-ADGroup and Get-ADGroupMember The basic flow is to extract the AD groups - place them into a DataTable and then iterate through the DataTable rows... Read more →

How to fix Database might contain bulk logged changes that have not been backed up for Always On

Question: I'm trying to add a new database to an existing Clustered Always On High Availability Group , but every time I execute the following statement , I get the error message below alter availability group MY_AG1 add database [mydb]; Msg 1475, Level 16, State 105, Line 5 Database "mydb" might contain bulk logged changes that have not been backed up. Take a log backup on the principal database or primary database. Then restore this backup either on the mirror database to enable database mirroring or on every secondary database to enable you to join it to the availability group.... Read more →

Does SQL Server require a restart after memory change

Question: I’d like to increase the memory allocated to our SQL Server instances. Once the ‘maximum server memory…’ is increased, are there any SQL services which should be restarted? Answer:-You don’t need to restart SQL Server, adjusting SQL Server’s Max Server memory is an online option . When you do make the change,you’ll highly likely cause data or procs to leave their caches so queries could be slower for a short while after you run it. If your environment is sensitive to performance consider making this change in a non production window. Ensure you leave enough memory on host for... Read more →

How to debug Kerberos and LDAP for SQL Server

Question: I've deployed Container based SQL Servers and set up integrated authentication with Active Directory. There are some issues and I need to explore error messages for some AD failures. How can I generate a log to gather trace statements and responses? Answer: It is possible to gather this information by enabling the SQL Server - SQLPAL - Logging . Part of enabling SQLPAL includes configuring the location for the output log file. By default SQLPAL is turned off. Add following to /var/opt/mssql/logger.ini. If the logger.ini is not present , than create the file in the /var/opt/mssql/ path. If it... Read more →

Understanding SQL Server Orphaned Users

SQL Server Orphaned Users are a typical item on security vulnerability reports. The next step is to understand why and when SQL orphaned users occur , and thinking about how to set up a process to report and actions to fix. There are multiple reasons why the SQL Server database Orphaned user can occur Example 1 : When a database is moved or migrated, the database users in the migrated or moved database are not mapped to a specific SQL Server login on the new SQL Server instance. Example 2 : A SQL database user for which the corresponding SQL... Read more →

How to identify sql server version for target Podman or Docker Container Imager

Question: I'm setting up different Podman and Docker based Containers for SQL Server. I'd like to know , how can I identify the SQL Server version & build number , if I'm using the mcr.microsoft.com/mssql/server:2019-latest convention, when choosing which image to use? Based on the information it isn't possible to tell the version and build numbers An example podman command: docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=myPassw0rd" \ -p 1433:1433 --name sql1 \ -d mcr.microsoft.com/mssql/server:2019-latest Answer: It is possible grab the version number , build and some other information which may be useful Execute this Podman command. Note: you can also... Read more →

Find SQL database Orphaned Users SQL script

Question: Do you have a sql script to list database orphaned users. ? I need to run a daily report , checking if orphaned database users exist and then having a process to work with the application owners to clean up. Answer: A standard script used is below. One of the keys is to differentiate between database users which are orphaned versus database users which require to be remapped. An orphaned sql database user should have an value of "INSTANCE" in the column "authentication_type_desc" in the sys.database_principals, while a sql database user without login should have a value "NONE" SELECT... Read more →