Our yearly cabin opening “men’s weekend” was last weekend. The fishing was a bit below average, but we still had a great time. I brought my six year old son up, and two of my six year old nephews were brought up by their dad’s as well. The first day of fishing we went over to the rainbow flowage, which was a bit of a bust. We ended up hooking five or six northern pike, but only landed one because they kept biting off the line. The boys in the other boat caught some bluegill and bass, but overall the fishing wasn’t as hot as in years past. This was probably due to all the rain they got up there. There were places where the rivers were over the road. Below is the one pike we managed to land.
Security these days is as important as ever. SQL Server provides lots of ways to improve security of your data and environment. One small way I like to keep an eye on my system is to log sysadmin logins to the server. Sysadmin is able to do anything in SQL Server, and by reviewing my log from time to time I can be sure that no one is using this type of login to compromise the system.
The first thing to do is to create a table to hold the log data:
USE [DBA_DB] GO CREATE TABLE [dbo].[sysadminLogging]( [SAL_id] [INT] IDENTITY(1,1) NOT NULL, [Login] [VARCHAR](255) NOT NULL, [LoginDate] [DATETIME2](7) NOT NULL, CONSTRAINT [PK_sysadminLogging] PRIMARY KEY NONCLUSTERED ( [SAL_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
All that is left is to create a server level login trigger to record any sysadmin logins to that table:
CREATE TRIGGER [servertrigger_CheckForSysAdminLogin] ON ALL SERVER FOR LOGON AS BEGIN IF IS_SRVROLEMEMBER('sysadmin') = 1 BEGIN INSERT INTO DBA_DB.dbo.sysadminLogging ( [Login] , LoginDate ) VALUES ( ORIGINAL_LOGIN() , GETDATE() ); END; END; GO
Check this table regularly to get a good idea of who is logging into your SQL Server with these higher privileges.
james youkhanis points out a problem that can occur in the comments. This problem could make logging in quite difficult. I have posted a follow-up where I explain the problem, provide a workaround to allow logging in (as james demonstrates below), and provide an updated trigger to minimize the risk of this occurring.