How to secure Powershell

I use Powershell extensively in supporting SQL Server & other applications .Powershell is a significant component of the Windows operating system. I won't go into detail but there is also the progress of Powershell Core - which is the multiplatform option Recently there has been rising concern about fileless malware access exploiting Powershell . There are significant benefits in exploiting Powershell - one of the main reason being that Powershell allows a deep access to Windows core. The very aspect of Powershell which makes it so valuable and effective in managing SQL Server and other Windows services also potentially opens... Read more →

How to get database role securables

Question: I need to extract the securables for a user created SQL Server database role. For example , the Explicit Permissions including the Permission,Grantor,Grant,With Grant and Deny. And also the Securables - Schema,Name,Type How can I get this information via t-sql? Answer: Initially you can check the SQL Server Profiler and trap the actual sql query executed as part of generating the display. An example of the query is : declare @name varchar(20) set @name = 'myuser' SELECT ascii(prmssn.state) AS [PermissionState], null AS [Code], grantor_principal.name AS [Grantor], prmssn.type AS [SqlCodePP], CASE WHEN (prmssn.class=4 or prmssn.class=101 ) THEN CASE (SELECT oc.type... Read more →

How to parse sys.fn_xe_file_target_read_file faster

Question: I've captured extended events messages into an xel file, and trying to read from the file with this query using the Extended Events function sys.fn_xe_file_target_read_file. I've discovered it parses the file very slowly. Is there a way of speeding up the parsing performance? SELECT [XML Data], [XML Data].value('(/event[@name=''error_reported'']/@timestamp)[1]','DATETIME') AS [Timestamp], [XML Data].value('(/event/action[@name=''database_name'']/value)[1]','varchar(max)') AS [Database], [XML Data].value('(/event/data[@name=''message'']/value)[1]','varchar(max)') AS [Message], [XML Data].value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)') AS [Statement] FROM (SELECT OBJECT_NAME AS [Event], CONVERT(XML, event_data) AS [XML Data] FROM sys.fn_xe_file_target_read_file ('C:\tmp\CaptureErrors_0_1.xel',NULL,NULL,NULL)) as FailedQueries Answer: My recommendation is to dump the data into either a temp table or permanent table. Once you've dumped the data ,... Read more →

Quick access to Failover Clustering cheatsheet

--return each node in the failover failover cluster instance SELECT NodeName, status, status_description, is_current_owner FROM sys.dm_os_cluster_nodes; --is the sql server configured in a failover cluster SELECT SERVERPROPERTY('IsClustered') --determine the underlying node where the Clustering is currently on SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') --sql server machine name SELECT SERVERPROPERTY('MachineName') --sql server instance name SELECT SERVERPROPERTY('InstanceName') --sql server server name SELECT SERVERPROPERTY('ServerName') --SQL Server in-built function to report nodes of the Cluster SELECT * FROM fn_virtualservernodes() --SQL failover cluster instance shared drive SELECT * FROM fn_servershareddrives() --SQL failover cluster instance shared drive SELECT * FROM sys.dm_io_cluster_shared_drives Read more →

SQL Server Performance Tuning

OLAP performance tuning SQL Performance tuning - Asking the right question Performance Tuning by Committee - SQL Performance tuning - Drilling into the problem The future of the SQL Server Performance Tuning Expert Strategies for sql server performance tuning Performance Tuning – The wrong way Notes on Performance Tuning very large databases for OLTP Top 5 habits of Performance Tuning Experts How to start SQL Performance Tuning What is SQL Server Query Tuning? SQL Server Performance Checklist Disk IO performance , disk block size tuning and SQL Servers (SQL ... netstat monitoring and tuning performance Performance Monitor Counters for SQL... Read more →

How to get the Always On Availability Group DNS name per database

Question: How can I get a list of databases on an Always On Availability Groups setup , along with the DNS name ? I need the DNS name of the listener hostname Answer: This query will list a group of databases associated with availability groups in a Clustered Always On setup . The sys.availability_group_listeners view as defined on Microsoft docs "For each Always On availability group, returns either zero rows indicating that no network name is associated with the availability group, or returns a row for each availability-group listener configuration in the Windows Server Failover Clustering (WSFC) cluster." This query... Read more →

How to restore Always On Availability Group databases

Steps to restore a database from a backup device that was part of an Always On Availability Group, and now needs to be restored Recovery Scenario : Requesting an older database copy previously backed up Name of Always on Availability Group = MyAG1 Name of Always On Availability Group db = MyAGDB1 Note: this is a workflow - and there may be some slight variations depending your Availability Group set up Steps to Restore database --Remove the database from the AG Group from the PRIMARY ALTER AVAILABILITY GROUP [MyAG1] REMOVE DATABASE [MyAGDB1]; --Restore the database to PRIMARY from the backup... Read more →