Red Gate SQL Monitor Metric – New logins to the Sysadmin role.

Red Gate SQL Monitor Metric – New logins to the Sysadmin role.

Another one of my SQL Monitor custom metrics has been published by Red Gate.  This one generates an alert when the count of server principles who are added to the sysadmin server role changes.  It is very useful when multiple members of an IT team may have a DBA support function and are inclined to simple add users with sysadmin roles. This metric can be used to alert when this happens so it can be checked and verified in case it causes a security risk.

Advertisements

Weekly SQL: Filter on SP_WHO2 in a table.

This is a new section of my blog, where I plan to publish a SQL script on a weekly basis.  These will be scripts I either use on a daily basis or ones which I find particularly interesting and worth sharing.

This week, the following script is really useful for finding out who is connected to a particular database. It uses the SP_WHO2 special procedure and allows the data to be filtered.  I am not sure where I got this script from, but it is not mine, but I must use it every day:

Run this on the database you wish to query:

DECLARE @Table TABLE(
SPID INT,
Status VARCHAR(MAX),
LOGIN VARCHAR(MAX),
HostName VARCHAR(MAX),
BlkBy VARCHAR(MAX),
DBName VARCHAR(MAX),
Command VARCHAR(MAX),
CPUTime INT,
DiskIO INT,
LastBatch VARCHAR(MAX),
ProgramName VARCHAR(MAX),
SPID_1 INT,
REQUESTID INT
)

INSERT INTO @Table EXEC sp_who2

SELECT *
FROM @Table
where dbname = db_name(db_id())

You can add to the where clause to filter on the different columns.

I also use this script if I am restoring a database and need to quickly kill of any users.  By adding the following extra code after the above, it will generate a the kill commands which I can copy and paste to a new query window:

–Generate a kill script
SELECT ‘kill ‘ + cast(spid as varchar(3))
FROM @table
WHERE dbname = db_name(db_id())

First Redgate SQL Metric published

First Redgate SQL Metric published

A while ago, my first Red Gate SQL metric was published for use with Red Gates excellent SQL Monitor software.  This metric allows you to be alerted when a transaction log grows over a defined threshold.  This can be particularly useful if a process is stopping the log from truncating or the transaction backups have not been running successfully.