How to find user who ran DROP or DELETE statements on your SQL Server Objects by Manvendra Singh

http://www.mssqltips.com/sqlservertip/3090/how-to-find-user-who-ran-drop-or-delete-statements-on-your-sql-server-objects/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20131202

Problem

Someone has dropped a table from your database and you want to track who did it.  Or someone has deleted some data from a table, but no one will say who did.  In this tip, we will look at how you can use the transaction log to track down some of this information.

Solution

I have already discussed how to read the transaction log file in my last tip "How to read SQL Server Database Log file". Before reading this tip, I recommend that you read the previous tip to understand how the transaction log file logs all database activity.

Here we will use the same undocumented function "fn_dblog" to find any unauthorized or unapproved deletes or table drops. This tip will help you track or find any unethical or an unwanted user who has dropped a table or deleted data from a table. I strongly suggest testing any undocumented functions in a lab environment first.

One way to find such users is with the help of the default trace, because the default trace captures and tracks database activity performed on your instance, but if you have a busy system the trace files may roll over far too fast and you may not be able to catch some of the changes in your database.  But these changes are also tracked in the transaction log file of the database and we will use this to find the users in question.

.... follow above link

Monitoring Databases changes using WMI alerts by Svetlana Golovko

Problem

We need to get notifications every time a database is created or deleted. Also, we would like to receive emails when there are any database level configuration changes. This will help us react to these changes immediately (for example, configure database backup for a new database, configure transaction logs backups when the database recovery mode is changed, update maintenance plans and inventory when the database is deleted etc.).

Solution

In this tip you can find out how to setup email alerts using DDL triggers when new databases are created. In our tip we will explain how to setup universal notifications for databases modifications using WMI alerts.

SQL Server Agent Configuration

http://www.mssqltips.com/sqlservertip/3095/monitoring-databases-changes-using-wmi-alerts/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20131115

Disaster Recovery Planning for Microsoft SQL Server - Getting Backups and Restores Right by

http://www.mssqltips.com/sqlservertip/2950/disaster-recovery-planning-for-microsoft-sql-server--getting-backups-and-restores-right/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20130625

Querying SQL Server Index Statistics by Dattatrey Sindol

Problem

Indexes are a great way to improve the performance of queries. However, they require regular monitoring and maintenance to ensure that the system continues to function smoothly. If indexes are not monitored regularly and appropriate action not taken then it can have a negative impact on the system's performance. Fortunately SQL Server has a feature which makes it easy for us to monitor the indexes and do some analysis on their utilization. In this tip we will take a look at different ways to get information about SQL Server indexes.

Solution: look on below

http://www.mssqltips.com/sqlservertip/2979/querying-sql-server-index-statistics/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20130702

Give a user just ONE table access by Mike Hinds

http://www.sqlservercentral.com/Forums/Topic982196-359-1.aspx









What we finally did was:

EXEC sp_msForEachTable 'DENY SELECT ON ? TO [TheUser]'
GO

GRANT SELECT ON [dbo].[AllowedTable] TO [TheUser]
GO

For anyone considering this, sp_msForEachTable is "non-supported". 
Regards, Mike

Running Queries on Excel Spreadsheets using SQL Server 2008

http://colinmackay.co.uk/2011/08/12/running-queries-on-excel-spreadsheets-using-sql-server-2008/

By Erin Stellato, 2013/01/23

Capturing Baselines on SQL Server: Where's My Space?

http://www.sqlservercentral.com/articles/baselines/96059/

Storage is an integral part of a SQL Server installation. We need to make sure that the capacity and performance of the storage subsystem match our requirements in terms of the volume of data we need to store, over time, and the speed with which we need to access it.

In this article, we'll tackle the topic of monitoring disk space usage. By tracking how much is in use and how much is still available, over time we'll have the data we need for better capacity planning, and can ensure that a database won't ever run out of disk space. It is easy to find out how much space is free on a drive right now, but trending that information requires foresight and a bit of data capture. Fortunately, both are easy to do in SQL Server, as we'll see here.

In order to work through this article, you'll need to create a database in which baseline information can be stored. My previous post, Back to Basics: Capturing Baselines on Production SQL Servers, created one called BaselineData, and all the scripts included in this post assume this database exists, and that you're using SQL Server 2005 or higher.

The Data to Capture

In order to understand how much space databases are using, and how much is free, we need to capture file information for data and log files from either the sys.database_files or sys.master_files catalog views. If you're using SQL Server 2012, then you can also get file size information for all databases from the sys.dm_db_file_space_usage DMV. However, for SQL Server 2005 through SQL Server 2008R2, it provides this information only for the tempdbdatabase.

Use of sys.master_files is preferable for capturing file information, as it is a system-wide view and it means we don't need to roll through each database, querying sys.database_files to retrieve information. However, neither of these views list how much of the file is in use, only the size of the file.

It is a good practice to size database files in advance, and grow them a manually, as necessary. This helps avoid the performance hit when files grow automatically, particularly during periods when the server is busy. As such, it is essential the DBA knows how much space is in use within a database file in order to determine when to grow a file manually, and therefore prevent auto-growth.

File growth and instant file initialization

Enabling instant file initialization reduces the cost of growth events for data files. However, log files cannot utilize this feature.

We can capture space usage within a file using the FILEPROPERTY function, but FILEPROPERTY is database-scoped, so when we call the function and pass in the logical file name, from sys.master_files, we get the usage information for only the local database. Ultimately, we end up querying sys.database_files for each database

....

Email alerts when new databases are created in SQL Server

Chad Churchwell

Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication. He has been in IT for 14 years, working with SQL Server for 10 years, and is currently a enior DBA Consultant with Pragmatic Works. He is active in the community speaking at several SQL Saturday events, as well as maintaining a blog atwww.sqlchad.com.

http://www.mssqltips.com/sqlservertip/2864/email-alerts-when-new-databases-are-created-in-sql-server/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2013112

USE master
GO
CREATE TRIGGER trg_DDL_CreateDatabase
ON ALL SERVER
FOR CREATE_DATABASE
AS
declare @results varchar(max)
SET @results = 
  (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
GO

USE master
GO
CREATE TRIGGER trg_DDL_CreateDatabase
ON ALL SERVER
FOR CREATE_DATABASE
AS
declare @results varchar(max)
declare @subjectText varchar(max)
SET @subjectText = 'DATABASE Created on ' + @@SERVERNAME + ' by ' + SUSER_SNAME() 
SET @results = 
  (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'SQLAlerts',
 @recipients = 'SQLDBAGROUP@YOURDOMAIN.COM',
 @body = @results,
 @subject = @subjectText
GO