How to capture SQL login details with Extended Events

An application owner wanted to record the usage of a specific logon and assess where the connections were coming from 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 →

How to get SQL port number using xp_readererrorlog

If using a fixed port or dynamic port , get the xp_readererrorlog via scanning Read more →

Query VMWare vCenter VCDB database for guest power state = OFF

Question: I want to identify virtual machines which are powered off. What query can I run which will return the full list of virtual machines within a VMWare vCenter database where the power state = OFF. Answer: It is possible to return the recordset by querying the database. Find a sample query executed on a vcDB database. The query includes some extra information , which you may find useful . The key column is the POWER_STATE column in the vpxv_vms table. There are different potential power states for a virtual machine . Possible values are off, on, and suspend SELECT... Read more →

How to find the local tcp port used on SQL Server

Question: How can I find the tcp port number used by a session ? I want to use a SQL Query to find this information as I don't have access to the cmdline. Answer: One method to use from within SQL Server is the sys.dm_exec_connections DMV . This SQL Server DMV returns various details about the current connections to a SQL Server Instance. This query returns connections information for all existing connections. One of the columns is the "local_tcp_port" , and when the net_transport is TCP - there will be a value in the "local_tcp_port" SELECT suser_sname() as username ,local_tcp_port... Read more →