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 →

List the differences between Kerberos and NTLM

There is a message found in SQL Server Error Logs similar to The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/myserver.net:60000 ] for the SQL Server service. Windows return code: 0x200b, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered. The line I'm interested in reviewing is Failure to register a SPN might cause integrated authentication to use... Read more →

How To Use Transparent Data Encryption To Increase Your Security

In addition to a general database policy and practices of securing databases such as : Security architecture, Asset encryption ,Firewalls & Regular Audits , there is an option to apply Transparent Data Encryption (TDE) to a SQL Server database - aka "data at rest encryption". TDE is about securing data in the situation of backup device\drive theft or adhoc backup file protection. Setting up TDE in SQL Server is in summary Create a master key. Create or obtain a certificate protected by the master key. Create a database encryption key and protect it by using the certificate. Set the database... Read more →

Use Get-ADPrincipalGroupMembership with other Active Directory domains

Question: I use the Powershell cmdlet Get-ADPrincipalGroupMembership to get AD group membership of IDs within the current domain, but how can I use Get-ADPrincipalGroupMembership to check the membership of an ID in another domain? Answer: Yes , The Get-ADPrincipalGroupMembership cmdlet will return the group membership of the specified user , in the current domain of the logon executing this cmdlet . To access the group membership of a specific ID , you'll need to use the -server switch . Example Get-ADPrincipalGroupMembership 'ID1' -Server 'anotherdoamin' | select name If you want to read on how to access the group membership of... Read more →

How to find list of AD groups of a user from SQL Server

An application owner reported a sudden loss of access to database tables . They'd been reading from the sql tables - but logged off for awhile , logged back on and couldn't see the tables. I logged on , could see the tables , so discounted any possibilities they may have been dropped. So the next question was to confirm the user logon still had the relevant privileges to see and read from the tables. I needed to check the user was in certain Active Directory Groups. I wanted to check this from the SQL Server side - and one... Read more →