Tips
Auditing Products for SQL Server
With the many needs (SOX, HIPAA, internal, etc.) for auditing sensitive data, what are the options available in the market place to audit SQL Server data and code changes? Do these products automatically collect the data as well as build reports and selectively alert on critical issues?
Auditing SELECT statements in SQL Server 2008
Management has asked that I audit when data is read from a table in the database. I can't use triggers, because they don't fire on SELECT statements. Is there a method other than running a SQL Server Profiler or server side trace to audit when SELECTs are issued against tables?
Auditing sysadmin on multiple servers using PowerShell
I have a requirement to periodically audit for members of the sysadmin role across all my servers. I'd like to automate this and I've heard PowerShell is a good match. In order to feed a downstream governance application, I'd like to simply output the members into text files. In this tip we show you how.
Auditing your SQL Server database and server permissions
One of the things that DBAs need to be aware of is the permissions that are granted at the server level and at the database level. Using Enterprise Manager or Management Studio it is pretty easy to look at one object at a time, but what if you want to look at permissions you have granted across the board. Pointing and clicking is not so bad if you have a couple of objects to look at, but most instances of SQL Server have several layers of permissions that are granted. So what is the best way to get an overall look at your permissions that have been set?
Designing Tables for Audit Data in SQL Server
My company is just starting to look at adding functionality to retain historical data for key tables and columns for auditing purposes in many of our SQL Server databases. I have seen some of your recent tips related to triggers (Forcing Trigger Firing Order in SQL Server and Trigger Alternatives in SQL Server - OUTPUT Clause). Based on using triggers or a similar technology, what is the best way to store the historical data? What options are available to store the data? Are there really a variety of options? Should the historical data be in the same database as the original data? Does it really make a difference?
How do I enforce SQL Server 2000 password changes?
One of the simplest security best practices is changing passwords on a regular basis. For some organizations that could be quarterly while others may have a more aggressive policy. Regardless of the policy, the issue remains the same, how can I enforce SQL Server 2000 password changes for my logins?
Identify Orphaned Windows Logins and Groups in SQL Server with sp_validatelogins
It's audit time and I'm being asked to verify all the Windows-based logins on my Microsoft SQL Server instances. How do I go about doing this? We have a dedicated Security Team that manages all the Windows logins for our domain and their work only crosses with ours when I, as the DBA, request that an Active Directory (AD) group is created for use as a SQL Server principal on one of my servers. We have cases where individual AD accounts have been created as SQL principals (logins) and when staff transition in and out of positions (or employment within our organization for that matter) I am not made aware of the changes so I know we have Windows-based logins on our SQL Server instances that have no corresponding AD login in our domain. Gr
Identifying What Runs at Startup on SQL Server
My auditors have asked me to identify what runs when my SQL Servers first start up. In this tip we look at different things that can run when an instance of SQL Server starts.
Password management options for the SQL Server sa login
In earlier tips from the sa series we outlined 'When not to use the sa password' and 'When was the last time the sa password changed?'. In this installment of the sa series we will be outlining options for password management. In a nutshell, depending on the security needs of the organization dictates how the sa password should be managed. Although at a certain level, the sa login needs to be protected even in environments without specific legal or regulatory requirements. As the security needs increase, then it is necessary to implement additional measures to manage and protect the most privileged (out of the box) login in SQL Server, the sa login.
SOX for SQL Server DBAs
I used to work for a small organization with a few SQL Servers. I have changed jobs and started working for a much larger publicly traded company. When I was at my last job, I heard a great deal of buzz about SOX and some other types of audits and legislation. Since it did not really pertain to me, I did not worry too much about it. Now that I am faced with it and a pending audit, I need to get up to speed quickly. As such, can you provide me with some basic information about SOX? What items I should be concerned about as a DBA? Where can I find more information before an upcoming audit?
SQL Server 2008 Audit Change Group
Auditing your SQL Server instances has become more of a concern these days. SQL Server 2008 introduced a new feature named SQL Server Audit. Enabling this feature can be done in just a few simple steps, but so could disabling this feature. And when it comes to audits, many times you are asked to provide proof that the audit itself has not been tampered with.
SQL Server 2008 Auditing Feature
Auditing your instances of SQL has become more of a concern these days. SQL 2008 introduced a new feature named SQL Server Audit. In this tip I will cover how to setup auditing.
SQL Server 2008 TSQL Auditing Commands for SELECT statements
In a previous tip on Auditing SELECT statements in SQL Server 2008, you have seen how you can use SQL Server Management Studio to audit SELECT statements in SQL Server 2008. While it is easy to use the graphical user interface to create audit objects, it would be a bit of a challenge if you would want to do this repeatedly. Are there equivalent Transact-SQL statements to accomplish the same task?
SQL Server Login Properties to Enforce Password Policies and Expiration
For some reason is your web site up, but none of the data is loading on your web site? Is your SQL Server up and running? Are you able to access the data with either your Windows account or sa? What does your SQL Server error log report? What is the culprit?
SQL Server Security Auditing to Match Logins And Users
I have been tasked with auditing security on my SQL Server. However, this needs to be a somewhat automated process as I don't want to have to rely on taking screenshots every month to satisfy our auditors. What tables and/or views should I be using and what's the best way to extract the information out of them?
Steps to restore a database that has a SQL Server Audit defined
When restoring a database that has audit specifications defined on a different server, the audit events are defined but the audit mechanism is not able to capture audit data. In this tip we walk though the steps you need to take to enable capturing of audit data for the database.
Using passwords with SQL Server database backup files
One issue when creating backups for SQL Server databases is that these backup files can be restored to any other SQL Server as long as the version of SQL Server supports the backup file that you are trying to restore. In most cases this is not an issue unless you are trying to restore a backup from 6.5 or earlier onto a SQL Server 7.0 or later installation. Because of this ability to restore the backups to any other SQL Server this exposes your data to potential theft or misuse of your data. Most of the data in backup files is highly compressible, so a backup that maybe 1GB can easily be compressed to about 200MB. So even your largest backup files could be vulnerable to someone copying them to their laptop or burning them to a DVD and t
Top 10
SQL Server Login Properties to Enforce Password Policies and Expiration
For some reason is your web site up, but none of the data is loading on your web site? Is your SQL Server up and running? Are you able to access the data with either your Windows account or sa? What does your SQL Server error log report? What is the culprit?
Identifying What Runs at Startup on SQL Server
My auditors have asked me to identify what runs when my SQL Servers first start up. In this tip we look at different things that can run when an instance of SQL Server starts.
Auditing sysadmin on multiple servers using PowerShell
I have a requirement to periodically audit for members of the sysadmin role across all my servers. I'd like to automate this and I've heard PowerShell is a good match. In order to feed a downstream governance application, I'd like to simply output the members into text files. In this tip we show you how.
Designing Tables for Audit Data in SQL Server
My company is just starting to look at adding functionality to retain historical data for key tables and columns for auditing purposes in many of our SQL Server databases. I have seen some of your recent tips related to triggers (Forcing Trigger Firing Order in SQL Server and Trigger Alternatives in SQL Server - OUTPUT Clause). Based on using triggers or a similar technology, what is the best way to store the historical data? What options are available to store the data? Are there really a variety of options? Should the historical data be in the same database as the original data? Does it really make a difference?
Auditing your SQL Server database and server permissions
One of the things that DBAs need to be aware of is the permissions that are granted at the server level and at the database level. Using Enterprise Manager or Management Studio it is pretty easy to look at one object at a time, but what if you want to look at permissions you have granted across the board. Pointing and clicking is not so bad if you have a couple of objects to look at, but most instances of SQL Server have several layers of permissions that are granted. So what is the best way to get an overall look at your permissions that have been set?
SQL Server Security Auditing to Match Logins And Users
I have been tasked with auditing security on my SQL Server. However, this needs to be a somewhat automated process as I don't want to have to rely on taking screenshots every month to satisfy our auditors. What tables and/or views should I be using and what's the best way to extract the information out of them?
Steps to restore a database that has a SQL Server Audit defined
When restoring a database that has audit specifications defined on a different server, the audit events are defined but the audit mechanism is not able to capture audit data. In this tip we walk though the steps you need to take to enable capturing of audit data for the database.
Auditing SELECT statements in SQL Server 2008
Management has asked that I audit when data is read from a table in the database. I can't use triggers, because they don't fire on SELECT statements. Is there a method other than running a SQL Server Profiler or server side trace to audit when SELECTs are issued against tables?
SQL Server 2008 TSQL Auditing Commands for SELECT statements
In a previous tip on Auditing SELECT statements in SQL Server 2008, you have seen how you can use SQL Server Management Studio to audit SELECT statements in SQL Server 2008. While it is easy to use the graphical user interface to create audit objects, it would be a bit of a challenge if you would want to do this repeatedly. Are there equivalent Transact-SQL statements to accomplish the same task?
Identify Orphaned Windows Logins and Groups in SQL Server with sp_validatelogins
It's audit time and I'm being asked to verify all the Windows-based logins on my Microsoft SQL Server instances. How do I go about doing this? We have a dedicated Security Team that manages all the Windows logins for our domain and their work only crosses with ours when I, as the DBA, request that an Active Directory (AD) group is created for use as a SQL Server principal on one of my servers. We have cases where individual AD accounts have been created as SQL principals (logins) and when staff transition in and out of positions (or employment within our organization for that matter) I am not made aware of the changes so I know we have Windows-based logins on our SQL Server instances that have no corresponding AD login in our domain. Gr
Last 10
Identifying What Runs at Startup on SQL Server
My auditors have asked me to identify what runs when my SQL Servers first start up. In this tip we look at different things that can run when an instance of SQL Server starts.
Auditing sysadmin on multiple servers using PowerShell
I have a requirement to periodically audit for members of the sysadmin role across all my servers. I'd like to automate this and I've heard PowerShell is a good match. In order to feed a downstream governance application, I'd like to simply output the members into text files. In this tip we show you how.
Steps to restore a database that has a SQL Server Audit defined
When restoring a database that has audit specifications defined on a different server, the audit events are defined but the audit mechanism is not able to capture audit data. In this tip we walk though the steps you need to take to enable capturing of audit data for the database.
SQL Server Security Auditing to Match Logins And Users
I have been tasked with auditing security on my SQL Server. However, this needs to be a somewhat automated process as I don't want to have to rely on taking screenshots every month to satisfy our auditors. What tables and/or views should I be using and what's the best way to extract the information out of them?
SQL Server 2008 Audit Change Group
Auditing your SQL Server instances has become more of a concern these days. SQL Server 2008 introduced a new feature named SQL Server Audit. Enabling this feature can be done in just a few simple steps, but so could disabling this feature. And when it comes to audits, many times you are asked to provide proof that the audit itself has not been tampered with.
SQL Server 2008 Auditing Feature
Auditing your instances of SQL has become more of a concern these days. SQL 2008 introduced a new feature named SQL Server Audit. In this tip I will cover how to setup auditing.
Identify Orphaned Windows Logins and Groups in SQL Server with sp_validatelogins
It's audit time and I'm being asked to verify all the Windows-based logins on my Microsoft SQL Server instances. How do I go about doing this? We have a dedicated Security Team that manages all the Windows logins for our domain and their work only crosses with ours when I, as the DBA, request that an Active Directory (AD) group is created for use as a SQL Server principal on one of my servers. We have cases where individual AD accounts have been created as SQL principals (logins) and when staff transition in and out of positions (or employment within our organization for that matter) I am not made aware of the changes so I know we have Windows-based logins on our SQL Server instances that have no corresponding AD login in our domain. Gr
SQL Server 2008 TSQL Auditing Commands for SELECT statements
In a previous tip on Auditing SELECT statements in SQL Server 2008, you have seen how you can use SQL Server Management Studio to audit SELECT statements in SQL Server 2008. While it is easy to use the graphical user interface to create audit objects, it would be a bit of a challenge if you would want to do this repeatedly. Are there equivalent Transact-SQL statements to accomplish the same task?
Auditing SELECT statements in SQL Server 2008
Management has asked that I audit when data is read from a table in the database. I can't use triggers, because they don't fire on SELECT statements. Is there a method other than running a SQL Server Profiler or server side trace to audit when SELECTs are issued against tables?
Designing Tables for Audit Data in SQL Server
My company is just starting to look at adding functionality to retain historical data for key tables and columns for auditing purposes in many of our SQL Server databases. I have seen some of your recent tips related to triggers (Forcing Trigger Firing Order in SQL Server and Trigger Alternatives in SQL Server - OUTPUT Clause). Based on using triggers or a similar technology, what is the best way to store the historical data? What options are available to store the data? Are there really a variety of options? Should the historical data be in the same database as the original data? Does it really make a difference?
