SQL Server Tacklebox- P38: This book, as with almost all books, started out as an idea. I wanted to accumulate together those scripts and tools that I have built over the years so that DBAs could sort through them and perhaps adapt them for their own circumstances. | 7 Securing access to SQL Server ServerName Service_Name Service_Account Serverl MSSQLServerOLAPService LocalSystem Serverl SQLAgent SRVSAT LocalSystem Serverl SQLBackupAgent LocalSystem Serverl SQLBackupAgent_SRVSAT LocalSystem Serverl SQLBrowser LocalSystem Serverl SQLSERVERAGENT LocalSystem Serverl NULL NULL Table Service credentials query results. While I do not use this query often it always saves me many frustrating minutes of trying to manually find the same information via tools such as Computer Management and Services. Surveillance To this point I have focused on finding logins users groups and service accounts. The queries presented so far have all been useful for managing many hundreds if not thousands of accounts all with some level of privilege to my SQL Server instances. However knowing who has access to the data and what level of access they have is only one aspect of security I want to touch on in this chapter. It is also crucial for the DBA to track such actions as failed login attempts and to audit as far as possible the actions of users once they are in amongst the data. In this section I will introduce three surveillance techniques to help with these issues Error Log interrogation with T-SQL DDL Triggers and Server-side Tracing. Error log interrogation Unlike a lot of DBAs that I know I do not scour the SQL Error logs daily. I tend to review them when looking for a specific error or when conducting a periodic security review. It is not that I think it is a waste of time to do it I just think that I 185 7 Securing access to SQL Server would much prefer to read the logs with T-SQL. Fortunately SQL Server offers two stored procedures to make this possible namely sp_enumerrorlogs and sp_readerrolog. As Figure shows sp_enumerrorlogs simply lists the available SQL Server error logs. EXEC sp enumerrorlogs Results j Messages Archive it Date Log File Size Byte 1 0 10 18 2008 10 53 152488 2 1 10 14 2008 14646 3 2 10 11 2008 10 50 16010 4 3 10
