How to check if Replication components are installed on your SQL Server instance

If you need to check as to whether or not SQL Replication components are installed on a specific instance of your SQL Server, executing the following command on that instance will give you the answer.

EXEC sp_MS_replication_installed

Not installed:

Installed:

Query for Change Data Capture (CDC) tables and columns

Real quickly, I was in need of a query that would give me a listing of the tables and columns involved in Change Data Capture (CDC). I wanted to see the tracked columns in one column to the right of the table name so I utilized FOR XML PATH. Below is what I came up with.

SELECT OBJECT_NAME(source_object_id) [Table Name],
      (SELECT name + ',' FROM sys.columns SC WHERE SC.object_id = CT.object_id AND name NOT LIKE '__$%' FOR XML PATH('')) [Columns],
 capture_instance,
 supports_net_changes,
 filegroup_name
 FROM CDC.change_tables CT

It's certainly not perfect but it should give you a good start if you are ever in need of such a query.

Sending Availability Group replication traffic through a dedicated network.

We recently set up a SQL 2012 HADR solution that took advantage of Availability Groups (AG’s). In this instance, we utilized a multi-subnet cluster to allow for the primary replica to live in our primary data center, while having the secondary replica in another data center.

Note: In this setup, only asynchronous replicas are allowed.

On each of the servers in the cluster, we have 3 network connections.

Public: All user traffic will flow through this connection

Private: For internal cluster communication

Replication: All AG traffic will be routed through this

After those dedicated connections are setup, database mirroring endpoints are used to receive connections other instances in the AG. You can read more about database endpoints here. Usually, when the endpoints are created in an AG, the following script is executed without specifying a specific IP to use.

--Replica1
CREATE ENDPOINT Hadr_Endpoint
AS TCP(LISTENER_PORT = 5022)
FOR DATA_MIRRORING(ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

ALTER ENDPOINT Hadr_Endpoint STATE = STARTED
GO

--Replica2
CREATE ENDPOINT Hadr_Endpoint
AS TCP(LISTENER_PORT = 5022)
FOR DATA_MIRRORING(ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

ALTER ENDPOINT Hadr_Endpoint STATE = STARTED
GO

Without specifying an IP that the endpoint is listening on, the listener will accept a connection on any valid IP. But, if we want to make sure our AG and user traffic are segregated, we must specify the IP of the Replication network connection. So the script would look like so:

--Replica1
CREATE ENDPOINT [Hadr_endpoint]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.10.10.89)) --<-- Your Replication IP here
    FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
ALTER ENDPOINT Hadr_Endpoint STATE = STARTED
GO

--Replica2
CREATE ENDPOINT [Hadr_endpoint]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.10.20.89)) --<-- Your Replication IP here
    FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
ALTER ENDPOINT Hadr_Endpoint STATE = STARTED
GO

You can reference the BOL article for creating an endpoint here

NOTE: Equally important when using the CREATE AVAILABILITY GROUP statement is to specify the replication IP in the ENDPOINT_URL section. By default, when scripting the AG, the FQDN will show in that section.

CREATE AVAILABILITY GROUP MyAG   
FOR       DATABASE MyDB1, MyDB2   
REPLICA ON      
'COMPUTER01' WITH         
(        ENDPOINT_URL = 'TCP://10.10.10.89:5022',
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
         FAILOVER_MODE = MANUAL          ),      
'COMPUTER02' WITH         
(        ENDPOINT_URL = 'TCP://10.10.20.89:5022',
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
         FAILOVER_MODE = MANUAL          );

You can read up on the CREATE AVAILABILITY GROUP statement here.

After this done, and you’ve completed the rest of the AG setup, replication traffic will be routed through your Replication network. A quick and easy test of this is to open up Windows Task Manager and watch the traffic on the three different Ethernet connections.

Note the adapter name for each connection.

To test this open up an SSMS connection to the AG listener and send a large amount of DML statements to a database within the AG. If you setup the endpoint with the default LISTENER_IP = ALL, you’ll most likely see a high amount of send and receive traffic on Public interface. But, if you’ve setup the endpoint to listen on the Replication IP, you’ll see a large amount of receive traffic on the Public interface (DML) and a high amount of send traffic on the Replication interface (sending the changes to the secondary replica).

Nodes are not consistently configured with IPv4 and/or IPv6 addresses on network adapters that are usable by the cluster.

During cluster validation of a multi-subnet SQL Server 2012 Availability Group cluster, the validation report kept returning the following warning:

"Nodes are not consistently configured with IPv4 and/or IPv6 addresses on network adapters
that are usable by the cluster."

Below that entry was as follows:

Node <Server1> configured with IP addresses from protocol IPv4
Node <Server2> is configured with IP addresses from protocol IPv4 and IPv6

After comparing ipconfig /all and device manager (show hidden devices) entries on both nodes, Server2 included 4 Microsoft ISATAP Adapters, while Server1 didn't.

Shown here as already disabled

After a couple hours of research and verification that we aren't using IPv6 on our network, I disabled the ISATAP devices inside the device manager and the cluster was able to validate properly.

SSRS Error: An item with the same key has already been added

This typically means that you have an column in your dataset query that is specified twice. For instance, SQL will let you return "Order Number" twice in a query, but if you try to use that query in SSRS, it will error with "An item with the same key has already been added".

Remove the second instance of the column or alias the column to a different name to keep the error from coming back.

Word Wrap Annotation in SSIS

I thought I would pass this on since there is no word wrap feature for annotations in SSIS. If you use CTRL+Enter inside the annotation box, SSIS will drop down to the next line to begin typing.

SQL Error in the Post Snapshot File for Transactional Replication

When using replication, it is sometimes useful to run a set of commands on the subscribing database either before or after the initial snapshot has been applied. By using a pre-snapshot file, a DBA could remove any dependencies in the subscribing database that would keep the table from being dropped in the event of a re-initialization. We use post-snapshot files to create indexes on the subscribing database that do not exist on the publisher. To find out whether or not pre/post snapshot files are being used, right click on the publication and choose properties. From there, select snapshot and look under the “Run additional scripts” section.

 

So when the subscription is initialized, a snapshot is prepared and placed in the default folder. Along with the bulk copy files, any pre or post snapshot files you are using are placed in there too. So the snapshot is applied to the subscriber and, in this case, a post-snapshot script is ran against the subscribing database. While the snapshot and the post-snapshot scripts are being ran, updates to the replicated tables are being queued for replication in the distribution database. These will be replicated to the subscriber once the snapshot has been applied and the post-snapshot script has completed.

But what happens in the event that the post-snapshot script has an error? The distribution agent considers this to be a failure and retries the post-snapshot script over and over again. While this is happening, updates that are queued for distribution are not being applied to the subscriber. How can we fix this?

One option is to open the post-snapshot file at the location under “Run additional scripts”, fix the error, reinitialize the subscription and re-create the snapshot. This might not be the best option depending on the size of your snapshot and the time it takes to generate it.

The second option is to directly open the post-snapshot file that was copied into the folder when the snapshot was created. By editing the post-snapshot file in the folder specified in the “Put files in the default folder” option, we are directly modifying the SQL that the distribution agent is executing and failing on. If time is critical, you may want to remove all SQL from the file and save it. Note: If you do this, you will have to manually apply the SQL to the subscriber. Once the error in the file either is fixed or the file is cleaned out, the distribution agent will open the file and complete successfully. After that, all the queue updates, inserts and deletes can flow from the publisher to the subscriber.

Finding Open Cursors (And Their Locks) In SQL Server

If you need information regarding open cursors on your SQL Server, you can query the sys.dm_exec_cursors DMV to get information on when they were created and their various properties. With that DMV along with the sys.dm_exec_sessions DMV and the sys.dm_exec_sql_text DMF to get the SQL that was used to create the cursor.

SELECTc.session_id
,c.cursor_id
,c.properties
,c.creation_time
,c.is_open
,c.fetch_status
,c.dormant_duration
,s.login_time
,t.text
FROM
sys.dm_exec_cursors (0) c
JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(c.sql_handle) t

If you need to see if one of those cursors is holding locks on your system, you can use the sys.dm_tran_locks and sys.dm_exec_sessions DMV 's along with the sys.partitions catalog view to see the cursor and the table it has locked.


SELECTOBJECT_NAME(P.object_id) AS TableName
,L.resource_type
,L.resource_description
,L.request_session_id
,L.request_mode
,L.request_type
,L.request_status
,L.request_reference_count
,L.request_lifetime
,L.request_owner_type
,s.transaction_isolation_level
,s.login_name
,s.login_time
,s.last_request_start_time
,s.last_request_end_time
,s.status
,s.program_name
,s.login_name
,s.nt_user_name
--,c.connect_time
--,c.last_read
--,c.last_write
--,t.text
FROM
sys.dm_tran_locks L
JOIN sys.partitions P
ON L.resource_associated_entity_id = P.hobt_id
JOIN sys.dm_exec_sessions s
ON L.request_session_id = s.session_id
--JOIN sys.dm_exec_connections c
--ON s.session_id = c.most_recent_session_id
--CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE
L.request_owner_type = 'CURSOR'
ORDER
BY L.request_session_id

Starting a SQL Agent job with Powershell and Windows Scheduled Tasks

In the past, I’ve needed to start SQL Agent jobs using Windows Scheduled Tasks. Using Powershell, this is now easier than ever.

First, let’s create the Powershell script. It’s very basic and relies on Windows Authentication and SQL Native Client to connect to the SQL Server.

#Vars for Server and JobName
$Server = "<Server>"
$JobName = "<JobName>"

#Create/Open Connection
$sqlConn = new-object System.Data.SqlClient.sqlConnection "server=$Server;database=msdb;Integrated Security=sspi"
$sqlConn.Open()

#Create Command Obj
$sqlCommand = $sqlConn.CreateCommand()
$sqlCommand.CommandText = "EXEC dbo.sp_start_job N'$JobName'"

#Exec Command
$sqlCommand.ExecuteReader()

#Close Conneection
$sqlConn.Close()


In a nutshell, this script will need you to set 2 variables, $Server and $JobName. Once those have been set, the script will open up a connection to the server, execute the sp_start_job command and then close the connection.

Next, we want to start the Powershell script via Windows Scheduled Tasks. When setting up the task, choose "Start a program" under Action and type powershell.exe . We specify the Powershell script to execute in the additional arguments. By adding &'\\<filepath>\StartTest_SQL_AgentJob.ps1' Powershell will start up and execute the script.

Note: If your <filepath> has spaces in it, you will need the "&", otherwise it can be omitted.

Resource Governor - A Practical Example

If your running SQL Server 2008 Enterprise and have the need to restrict certain users or groups from executing runaway queries, then the Resource Governor might be what you are looking for. By creating the right mix of workload groups and resource pools, a DBA can create an environment that is more predictable and stable, while maintaining the availability of resources for all users.

In this example, I'll create a Workload Group that only Windows logins will use. I used the distinction of Windows logins vs SQL logins because all the produciton applications hitting this server use SQL authentication while any users (aside from the sys admins) doing ad hoc analytics use Windows authentication. Many times when users are doing ad hoc analytics, they are digging through the data and looking for trends, which lends itself to someone using "Select *".  That kind of query on very wide and deep tables can easily lock a table needed for the production applications. This is where the resource governor can help. By limiting the amount of resources a query or a session can have, a DBA can make other sessions on the server execute more predicitbly.

There is quite a bit of setup involved with the Resource Governor and there are many different ways to do it, so that steps I take below will obviously have to be tailored to your enviornment.

There are 3 main parts to setting up the Resource Governor:
   1.       Resource Pool: A pool of resources that Workload Groups will access.
   2.       Workload Group: Group that logins belong to based on the Classifier Function.
   3.       Classifier Function: Function that assigns logins to Workload Groups.

First , let's do the setup for the classifier. Since we are classififing on login type (SQL or Windows) our Classifier Function should be quite simple, but the Classifier Function has to be schema bound, which means we can't reference sys.server_principals. So since we can't use the system tables in the Classifier Function, we need to create our own table in the MASTER database to keep SQL login information.

CREATE TABLE [SQLLoginsList]
(SQL_LoginName sysname)

Now we need to populate the table with all the existing SQL Logins.

Insert Into SQLLoginsList
Select Name From sys.Server_Principals Where [Type] = N'S'

Once the table is in place, a server trigger needs to be created to insert into the SQLLoginsList table any time a new SQL Login is created.

SET ANSI_NULLS ON

GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [srv_trg_SQLLoginList] ON ALL SERVER
       WITH EXECUTE AS 'sa'
       FOR CREATE_LOGIN
AS
       SET NOCOUNT ON
       SET CONCAT_NULL_YIELDS_NULL ON
       SET ANSI_PADDING ON
       SET ANSI_WARNINGS ON

       IF (SELECT   EVENTDATA().value('(//LoginType)[1]', 'VARCHAR(255)')) = 'SQL Login'

          BEGIN
                IF NOT EXISTS ( SELECT  *
                                FROM    SQLLoginsList
                                WHERE   SQL_LoginName = EVENTDATA().value('(//ObjectName)[1]','VARCHAR(255)') )
                   BEGIN
                         INSERT dbo.SQLLoginsList(SQL_LoginName)
                         SELECT  EVENTDATA().value('(//ObjectName)[1]','VARCHAR(255)')
                   END  
          END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [srv_trg_SQLLoginList] ON ALL SERVER

GO

Now that we have a place to query SQL Logins and a mechanism to catch newly created ones, we can start creating our Resource Pool and Workload Group.

When creating a Resource Pool, there are 4 properties that can be set.

   1.       MIN_CPU_PERCENT: The minimum CPU bandwidth for the pool when there is CPU contention.
   2.       MAX_CPU_PERCENT: The max CPU bandwidth for the pool when there is CPU contention.
   3.       MIN_MEMORY_PERCENT: minimum amount of memory reserved for this pool.
   4.       MAX_MEMORY_PERCENT: maximum amount of memory requests in this pool can consume.

The "when there is CPU contention" because CPU contention is a soft limit, meaning when there is no CPU contention the pool will consume as much CPU as it needs.

CREATE RESOURCE POOL pAdhocProcessing
WITH
 (MIN_CPU_PERCENT = 0 --no min cpu bandwidth for pool WHEN THERE IS CONTENTION
 ,MAX_CPU_PERCENT = 25 --max cpu bandwidth for the pool WHEN THERE IS CONTENTION
 ,MIN_MEMORY_PERCENT = 0 --no memory reservation for this pool
 ,MAX_MEMORY_PERCENT = 25 --max server memory this pool can take
 )

Next comes the creation of the Workload Group. By default there is already a group called "default" that all users are placed into if they aren't placed into another Workload Group by the Classifier Function. Think of the Workload Groups as buckets of users that can dip water (resources) from an assigned "pool" (Resource Pool). Each Workload Group can only have one Resource Pool, but a Resouce Pool can have many Workload Groups assigned to it.

When creating a Workload Group, there are 6 properties that can be set.

   1.       IMPORTANCE: Gives a precedence or "importance" to CPU resources available in the Resource Pool.
   2.       REQUEST_MAX_MEMORY_GRNT_PERCENT: Amount of memory one request can take from Resource Pool.
   3.       REQUEST_MAX_CPU_TIME_SEC: Amount of total CPU time a request can have.
   4.       REQUEST_MEMORY_GRANT_TIMEOUT_SEC: Maximum amount of time a request will wait for resource to free up.
   5.       MAX_DOP: Max Degree of Parallelism a query can execute with. This option takes precedence over any query hint or server setting.
   6.       GROUP_MAX_REQUESTS: Amount of requests this group can simultaneously issue.

The following script will create the Workload Group and assign it to a Resource Pool

CREATE WORKLOAD GROUP gAdhocProcessing
WITH
  (IMPORTANCE = LOW --Low importance meaning the scheduler will execute medium (default) session 3 times more often
  ,REQUEST_MAX_MEMORY_GRANT_PERCENT = 25 --one person can only take 25 percent of the memory afforded to the pool
  ,REQUEST_MAX_CPU_TIME_SEC = 60 --can only take a TOTAL of 60 seconds of CPU time (this is not total query time)
  ,REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 60 --max amount of time a query will wait for resource to become available
  ,MAX_DOP = 1 --overrides all other DOP hints and server settings
  ,GROUP_MAX_REQUESTS = 0 --unlimited requests (default) in this group
  )

 Using pAdhocProcessing

At this point, we should see the following under Management --> Resource Governor



The Resource Governor will stay in "Reconfigure Pending" status until we create the Classifier Function and issue a Reconfigure Command to turn the Resource Governor on.

Finally, we'll create our Classifier Function that will place all incoming requests into either the gAdhocProcessing group or the default group. This function looks a lot like any other scalar function with the excepting that is had to be created using "With SchemaBinding". Based on the login type and whether the login is a sysadmin, the function will return what Resource Group the request should fall into.

CREATE FUNCTION fnLoginClassifier()

RETURNS sysname
WITH SCHEMABINDING
as
Begin
  Declare @Login sysname

  set @Login = SUser_Name()

  if (IS_SRVROLEMEMBER('sysadmin') = 1)  --Server Admins

    Return N'default'
  else if (Select Count(*) from dbo.[SQLLoginsList] Where SQL_LoginName = @Login) > 0 --SQL Logins
    Return N'default'
  else if @Login like '<domain>%' --Windows Logins
    Return N'gAdhocProcessing'

  --If nothing is returned, put it in the default group just in case

  Return N'default'
End
GO   

Now that the Classifier Function has been created we have to tell the Resource Governor to use that function as it's classifier.

ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fnLoginClassifier)

At this point, if we right click the  Resource Governor in the Object Explorer and choose properties we will should see the following.

The Resource Pool pAdhocProcessing has been created and the Workload Group gAdhocProcessing has been assigned to it. Also, the fnLoginClassifier function shows as the Classifier function name and the message at the top signals us that the Resource Governor has pending changes and that we need to issue a Reconfigure command to enable the governor.

Now all that's left to do is turn it on. We can do that by issuing the following command.

ALTER RESOURCE GOVERNOR RECONFIGURE

Once the Resource Governor is turned on, we can monitor the amount of sessions each login has open and what Workload Group they have been assigned to.

SELECT  WG.name

       ,COUNT(*)
       ,ES.login_name
       ,es.program_name
FROM    sys.dm_exec_sessions ES
        INNER JOIN sys.dm_resource_governor_workload_groups WG
        ON ES.group_id = WG.group_id
GROUP BY WG.name,es.login_name,es.program_name       
ORDER BY login_name 

If for some reason the Resource Governor is not working as it should, issue the following command to disable it.

ALTER RESOURCE GOVERNOR DISABLE

Since it is possible on a busy system to lock yourself out by configuring the Resource Governor incorrectly, you may have to sign in with the Dedicated Administrator Connection (DAC). That connection uses the internal Workload Group and cannot have it's resources altered. Once you have established a connection using the DAC, you will have the ability to either disable the Resouce Governor or remove the Classifier Function. If you remove the Classifier Function, all incoming connections will fall to the default group. To remove the Classifier Function, issue the following commands.

ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = NULL)
ALTER RESOURCE GOVERNOR RECONFIGURE

Remove an Article from Transactional Replication without dropping the Subscription

          While using Transactional Replication, there may come a time when you need to drop an article from a publication, a publication that may have one or more subscriptions. You would think that the process would be very similar to adding an article to the publication; open up the article and uncheck the box. Now, if there were no subscribers to the publication, this would not be an issue, but odds are that this publication has been used for some time and there are one or more subscribers to it. If this is the case, when you go to uncheck the article to remove it, you may receive an message stating that since there are subscriptions on this table, you will have to reinitialize this subscription and generate a new snapshot.

          So how can we get around re-initialization of the subscriber and new snapshot generation? We manually execute some of the replication stored procedures to remove the article and keep the snapshot from being invalidated.

First we must use sp_dropsubscription to remove the subscription to the individual article.

EXEC sys.sp_dropsubscription

    @publication = '<PublicationName>',
    @article = '<ArticleToDrop>',
    @subscriber = '<SubscribingServer>',
    @destination_db = '<DestinationDatabase>'
 
Next, we drop the article from the publication without invalidating the snapshot. We do that by executing sp_droparticle with the force_invalidate_snapshot set to 0.

EXEC sys.sp_droparticle @publication = '<PublicationName>',
    @article = '<ArticleToDrop>',
    @force_invalidate_snapshot = 0

Finding Stored Procedures Related to Replication

If you have transactional or merge replication configured in your environment, you may want to know what objects were create by replication in your subscribing database. For Stored Procedures, there’s nothing in the sys.objects or sys.procedures catalog views that denotes that a procedure is used with replication. Fortunately, there is a system table created in the subscribing database called MSreplication_objects. The query below will give you the Stored Procedures created by replication and the articles associated with them.

SELECT [publisher]
      ,[publisher_db]
      ,[publication]
      ,[object_name]
      ,[object_type]
      ,[article]
  FROM [MSreplication_objects]
 WHERE [object_type] = 'P'

Now that we know the procs that are related to replication, we can exclude them from sys.objects or sys.procedures when looking for user procs in the database.

SELECT *
 FROM   sys.procedures
 WHERE  name NOT IN (SELECT [object_name]
                     FROM   MSreplication_objects
                     WHERE  [object_type] = 'P')

Backing Up an Analysis Services Database

There are a lot of DBA’s out there who have to administer SQL Analysis Services, but haven’t the slightest clue on how to do anything more than set it up and grant security. After the database has been setup and cubes are being created, it will need to be backed up just like the SQL databases you are already familiar with. So how do you backup an Analysis Services database when you can’t use the T-SQL commands you know and love? The answer is Analysis Services commands.

Start by opening up a new Analysis Services XMLA Query and log into the Analysis Services instance. You can do this by selecting File -> New -> Analysis Services XMLA Query

It will open up what looks to be a T-SQL window but it will only accept XML commands against the Analysis Services database. The following query will back up a single Analysis Services database without encryption or compression.

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  <Object>
    <DatabaseID>Insert SSAS Database name here</DatabaseID>
  </Object>
  <File>file_location\file_name.abf</File>
  <AllowOverwrite>true</AllowOverwrite>
</Backup>

Insert your own values into the <DatabaseID >, <File>,<AllowOverwrite> fields and run the command. Once you have it working, you can schedule it in a SQL Agent Job just like any other backup job that you may have out there. In the job step, select a type of “SQL Server Analysis Services Command”

A more in-depth reference be found here: http://msdn.microsoft.com/en-us/library/cc917611.aspx#XSLTsection124121120120

Getting SQL Server Error Messages to Write to the Event Log

SQL Server alerts can give you valuable insight into issues with your servers and can track a multitude of different errors, messages and events. To view all the built-in system error messages, run the following script on Master database.

SELECT *

  FROM SYS.MESSAGES

 WHERE LANGUAGE_ID = 1033

1033 is the English language ID taken from the syslanguages table

SELECT MSGLANGID

  FROM SYSLANGUAGES

 WHERE NAME = 'US_ENGLISH'

In SQL Sever 2008 there are over 8900 different messages that can be returned from the server, all with varying severities and text messages. The event I’m going to focus on here is message_id 229, “The %ls permission was denied on the object '%.*ls', database '%.*ls', schema '%.*ls'”.

SELECT * FROM SYS.MESSAGES

 WHERE LANGUAGE_ID = 1033

   and message_id = 229

Message_id	Language_id	Severity	Is_event_logged	Text
229	1033	14	0	The %ls permission was denied on the object '%.*ls', database '%.*ls', schema '%.*ls'.

If I want to be alerted any time a user tries to call a stored procedure or query a table they dont have access to, I can create a SQL Agent Alert using the following procedure and further configuring the alert to email me.

EXEC msdb.dbo.sp_add_alert @name=N'Insufficient Permission Error',

            @message_id=229,

            @severity=0,

            @enabled=1,

            @delay_between_responses=0,

            @include_event_description_in=1,

            @category_name=N'[Uncategorized]',

            @job_id=N'00000000-0000-0000-0000-000000000000'

GO

After this is setup, you would expect that everytime this error is generated an email would be sent to you, but…… going back to the IS_EVENT_LOGGED column in the SYS.MESSAGES table, you see that this event is not logged and will not be sent to the Event Log.

So how do I get this error to log to the Event Log and then sent in an email? In SQL 2005 SP2+ and SQL 2008, the following command can be issued to modify the existing messages:

EXEC sp_altermessage

 @message_id = 229

,@parameter = 'WITH_LOG'

,@parameter_value = 'True'

Setting the parameter ‘With_Log’ to a value of true tells SQL that the triggering of this event will always result in the error being written to the Event Log

NOTE: If/when you decide that you no longer want to monitor this event, just disabling the alert will not be enough to keep the event from being logged to the Event Log. You must run the following script to keep it from being logged.

EXEC sp_altermessage

 @message_id = 229

,@parameter = 'WITH_LOG'

,@parameter_value = 'False'