How to get database role securables

Use t-sql find SQL user securables Read more →

Microsoft adutil - FAQ

Learn how to use MS Adutil for Integrated authentication for SQL Server on Containers Read more →

How to view Service Principal Names in Active Directory

In an environment with Active Directory and SQL Server using Kerberos ,Service principal names (spn) become important. Windows supports delegating to NTLM protocol if Kerberos is not available , but being able to use Kerberos is the primary method. NTLM (New Technology LAN Manager) is a propietery Microsoft authentication protocol.The NTLM protocol is considered a legacy authentication protocol with various vulnerabilities, which pose a security risk. It's based on a relatively weak cryptographic scheme and is vulnerable to various attacks. It's replaced with Kerberos, which is a lot more secure and recommended. NTLM authentication should only be used in a... 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 →

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 →