SQL SERVER – Restricting Access to Contained Databases using Logon Triggers

When I wrote the article on “A Simple Example of Contained Databases” lesser did I know how people might be planning to use this capability? Having said that, there are environments where people want to use the concept of contained databases yet have flexibility of restricting the users. Recently, a DBA friend of mine had an interesting requirement and I was pleasantly surprised with the question:

Pinal: Hi Buddy.

DBA: Hi Pinal, Sorry to disturb you in the middle of the night.

Pinal: No problems, please go ahead.

DBA: I have a query around Contained Databases.

Pinal: Sure, let me see if I can help you. Not sure though.

DBA: Well, contained databases creates users inside the database and it is also used for authentication right?

Pinal: You are correct in your understanding. Now is there a problem?

DBA: No, there is no problem. I am worried about contained database implementation.

Pinal: Hmmm I don’t quite understand. Did you get a chance to search my site for a simple sample of implementing the same?

DBA: I did read the article on SQLAuthority, which is not the problem. It is about auditing and security processes inside my company.

Pinal: That is an interesting point of view. Please tell me more.

DBA: In our company, we are very strict and want to track every logins which are getting authenticated inside SQL Server. With contained databases, I want to build a different process.

Pinal: Go ahead, I am all ears.

DBA: We have enabled the creation of Contained Databases on the server. I want to restrict the databases which can be created as Contained Databases.

Pinal: Wow, why would you want to do like this?

DBA: Simple, as a DBA and Server Administrator – want to make sure only authorized users and databases which can act as contained databases can use the capability. We don’t want random users to create contained database on the server.

Pinal: Interesting, yes it is quite possible and the idea here is to use Logon Triggers. Let me make sure I try to blog this someday.

This blog is completely inspired by this conversation and let us go through the implementation. Here are the steps:

  1. Create a table in Master to hold databases that can be used for authentication.
  2. Insert the list of databases into the created table.
  3. Create the Logon Trigger to restrict the login available
  4. Try to Logon into the DB as normal user and as Contained Database user.
  5. Delete an entry from our table and check by logging in.
  6. Cleanup the script.

So you might ask, why master database and not any other database? Since logon triggers are server-scoped objects, we will create any necessary additional objects in master.

Note: Please DONOT use the script as-is because we are creating a Logon trigger. Please test, verify and then use the below script.

Let me walk you through the script:

-- Step 1 - Create a table in Master to hold databases that can be used for authentication.
USE MASTER
GO
CREATE TABLE [dbo].[authenticated_DBs](dbs INT PRIMARY KEY);
GO
-- We want anyone to be able to access this data for read-only purposes
GRANT SELECT ON [dbo].[authenticated_DBs] TO PUBLIC;
GO

The first step is to create a table which will host all the authenticated DBs we want for this particular instance that the DBA is monitoring. We have created the same in the master so that only authenticated and authorized users can change this.

-- Step 2 - Insert the list of databases into the created table.
-- Add the DB id for all of the DBs authorized to authenticate
-- including/excluding master DB
INSERT INTO [dbo].[authenticated_DBs] VALUES (DB_ID('master'));
GO
-- Used the script from previous blog to create the Contained DB
-- http://blog.sqlauthority.com/2011/03/31/sql-server-denali-a-simple-example-of-contained-databases/
-- Insert database id for ContainedDatabase
INSERT INTO [dbo].[authenticated_DBs] VALUES (DB_ID('ContainedDatabase'));
GO

The next step is to identify which all databases can allow login process. In our example, make sure we have made an entry for “master” and the database called “ContainedDatabase” based on the script from the other blog post.

-- Step 3 - Create the Logon Trigger to restrict the login available
-- This logon trigger will verify the current logon matches with one of the authorized DBs.
-- If it does, it allows the logon process to continue,
-- otherwise it will rollback, causing the session to terminate
CREATE TRIGGER Logon_authenticated_dbs
ON ALL SERVER FOR LOGON
AS
BEGIN
IF
NOT (EXISTS(SELECT * FROM [dbo].[authenticated_DBs]
WHERE dbs IN (SELECT authenticating_database_id
FROM sys.dm_exec_sessions
WHERE session_id = @@spid)))
ROLLBACK;
END;
GO

The Logon Trigger is the tricky part of this whole blog. You can be innovative with other parameters too. But be careful when playing with Logon triggers as this can lock you out of your SQL Server instance. Please use it carefully and cautiously.

SELECT * FROM [dbo].[authenticated_DBs]

I am showing the two entries that are on my local machine at the moment. We will try to mimic the login process and see what happens.

-- Step 5 - Delete an entry from our table and check by logging in.
-- Your [ContainedDatabase] database id might not be 7. Change appropriately
DELETE [dbo].[authenticated_DBs] WHERE dbs = 7
-- Try to connect again using ContainedUser
-- ContainedUser fails to connect due to trigger
INSERT INTO [dbo].[authenticated_DBs] VALUES (DB_ID('ContainedDatabase'));
GO
-- Try connect again as ContainedUser. Now it should work.

If you are have an entry into our authenticated table and are a valid user in the “Containeddatabase”, then the Login process will succeed as shown below.

If the entry into the table [dbo].[authenticated_DBs] is removed, we will be presented with an error message as shown below.

We are likely to get a similar error message if we are connecting to a contained database which was created by a user and the DBA has not given explicit rights by adding an entry into the server node.

USE MASTER
GO
-- CAUTION: If you forget to do this and drop the table authenticated_DBs then you need to manually delete this trigger or else you can't get into master too
DROP TRIGGER Logon_authenticated_dbs
ON ALL SERVER
GO
DROP TABLE authenticated_DBs
GO

In case you write a bad trigger which is always going to fail then you would be locked out from the server. In those situation DAC connections can help. I have written this earlier

SQL SERVER – Fix : Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’.

Please make sure to do the cleanup else you are likely to get errors if you are using this script for testing purposes and are an user of Contained Databases in your environments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Use Instead of Trigger – Guest Post by Vikas Munjal – Koenig Solutions

A trigger is an exceptional sort of stored procedure which functions when we try to amend the data in a table like inserting, deleting or updating data.  It is a database object, executed automatically and is bound to a table. Fundamentally, triggers are classified into two types mainly-

  • Instead of Trigger
  • After Trigger

We know how we can insert, delete or update operations aligned with excessively intricate views to support natively through ‘Instead of Trigger’. In other words, we can use this trigger as an interceptor for an action attempted on our table.

Instead of Trigger is an important element which is emphasized in almost every SQL course.  Here, we will discuss the situation where we want to make a table column which can auto generate the customized sequence.  We can see an example of the same below-

Here, we don’t have to misunderstand the id column above to be an identity column. This column is of character data type . All we want is to autogenerate this column as it is displayed in the figure above.

Usage

‘Instead of Trigger’ can help us to easily solve the situation above. In ‘Instead of Trigger ‘ we insert the data into the virtual tables prior to checking the constraints.  As far as ‘After Trigger’ constraints are concerned, they are checked in the first place.  Data is then inserted into the virtual tables ( inserted and deleted tables).

We can consider the code mentioned below for better understanding-

CREATE TABLE [dbo].[Employee1](
[id] CHAR(10) PRIMARY KEY,
[name] VARCHAR(50)
)
GO
INSERT INTO [dbo].[Employee1]  VALUES('a1','John')
GO

Now, for  an id column, we need to automatically generate a2, a3, a4….. For this, we can write a code in an insert trigger. Therefore, everytime the trigger command occurs, the trigger fires and the next number is generated.

Let us consider the command mentioned under-

INSERT INTO [dbo].[Employee1] (name) VALUES('Aslam')

Now we will enter the data in the column (name). By doing so, we will be entering NULL values in the column (id). We have a primary key column in the (id) column. In a primary key, there is no permission for NULL. Therefore, the Primary Key constraint is violated.

In case, we make use of ‘After Trigger’, then constraints are checked prior to inserting the data into the implied table. The primary key constraint is violated in this case.  Therefore, we can’t put data into virtual table. As a result, we will not find the trigger firing. On the other hand, on making use of ‘Instead of Trigger’, data is inserted into the virtual table prior to the constraint check.

Therefore, our virtual (inserted) table will be as-

Instead of Trigger’s code will be now fired. It is written as-

--Instead of Trigger
CREATE TRIGGER AutoIncrement_Trigger ON [dbo].[Employee1]
instead
OF INSERT AS
BEGIN
DECLARE
@ch CHAR
DECLARE
@num INT
SELECT  
@num=SUBSTRING(MAX(id),2,1) FROM [dbo].[Employee1]
SELECT  @ch=SUBSTRING(MAX(id),1,1) FROM [dbo].[Employee1]
IF @num=9
BEGIN
SET
@num=0
SET @ch= CHAR( ( 1 + ASCII(@ch) ))
END
INSERT INTO
[dbo].[Employee1] (id,name) SELECT (@ch+CONVERT(VARCHAR(9),(@num+1))),inserted.name FROM inserted
END

Explanation of the Code Above

The trigger’s code gets the greatest value from the id column. This is done when we use MAX(id)function, parse the integer data and the character. Now with the use of substring function, put it in @ch and @num variables respectively.

When @num turns 9 then @num is reset to 0. The character is then increased to the next character.

For instance, if @ch= 'a' then
ASCII('a')=97
@ch=CHAR(1+97)=CHAR(98)='b'

Soon after, @num raises by 1 and gets coupled with the @ch variable. Then, it will be placed into the dbo.employee1 table.

Now we can run the commands mentioned under-

INSERT INTO [dbo].[Employee1] (name) VALUES('Aslam')
INSERT INTO [dbo].[Employee1] (name) VALUES('Alan')
INSERT INTO [dbo].[Employee1] (name) VALUES('Mike')
INSERT INTO [dbo].[Employee1] (name) VALUES('Rahul')
INSERT INTO [dbo].[Employee1] (name) VALUES('Vikas')
INSERT INTO [dbo].[Employee1] (name) VALUES('Vijay')
INSERT INTO [dbo].[Employee1] (name) VALUES('Vineet')
INSERT INTO [dbo].[Employee1] (name) VALUES('Rajat')
INSERT INTO [dbo].[Employee1] (name) VALUES('Alice')
SELECT * FROM [dbo].[Employee1]

With the information provided in the piece of writing above, we know how important is Instead of Trigger in SQL.   It provides a clear approach to modify views that we cannot change directly through DML statements (INSERT, UPDATE, and DELETE).

NOTE: It is always a good idea to design your system such a way that you do not need trigger to use. You can include the logic of trigger in your code as well in your procedure and avoid the usage of the trigger. Triggers are very difficult to debug as well adds lots of overhead to the system. There are many performance problems due to poor implementation of the trigger. This post is just created for demonstration of how triggers can be used in special cases.

Guest Author

The author of the article Vikas Munjal is SQL Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several IT certifications, such as Oracle 11g, SharePoint, SQL Training, Prince2, etc.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fix : Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’.

I had previously written two articles about an intriguing observation of triggers online.

SQL SERVER – Interesting Observation of Logon Trigger On All Servers

SQL SERVER – Interesting Observation of Logon Trigger On All Servers – Solution

If you are wondering what made me write yet another article on logon trigger then let me tell you the story behind it. One of my readers encountered a situation where he dropped the database created in the above two articles and he was unable to logon to the system after that.

Let us recreate the scenario first and attempt to solve the problem.

/* Create Audit Database */
CREATE DATABASE AuditDb
GO
USE AuditDb
GO
/* Create Audit Table */
CREATE TABLE ServerLogonHistory
(SystemUser VARCHAR(512),
DBUser VARCHAR(512),
SPID INT,
LogonTime DATETIME)
GO
/* Create Logon Trigger */
CREATE TRIGGER Tr_ServerLogon
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO
AuditDb.dbo.ServerLogonHistory
SELECT SYSTEM_USER,USER,@@SPID,GETDATE()
END
GO
/* Dropping Database AuditDB */
/* Please note login to Server again will
Produce Errors */
USE master
GO
DROP DATABASE AuditDB
GO

After the database is dropped and we try to login again the following error will be displayed.

Logon failed for login ‘SQL\Pinal’ due to trigger execution.
Changed database context to ‘master’.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)

This error could have been evaded if the Trigger was dropped prior to dropping database.

USE master
GO
DROP TRIGGER Tr_ServerLogon ON ALL SERVER
GO

Now, it will not be possible to logon to the database using SQL Server Management Studio. The only way to fix this problem is using DAC. Read more details about DAC  Using a Dedicated Administrator Connection to Kill Currently Running Query. Now let us take a look at the example in our case where I am using windows authentication to logon to the system.

Connect SQL Server using sqlcmd and -A option, which will establish DAC. Running the following command on command prompt will allow you to login once again in the database. I am specifying argument -d master which will directly enable me to logon to master database when DAC is connected.

C:\Users\Pinal>sqlcmd -S LocalHost -d master -A
1> DROP TRIGGER Tr_ServerLogon ON ALL SERVER
2> GO

I hope my explanation on logon triggers, DAC, and sqlcmd is clear to everyone. Let me have your thoughts about my present article.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Interesting Observation of Logon Trigger On All Servers – Solution

Does the title of this post trigger your mind? If you all remember, a few days back I had written an article on my interesting observation regarding logon triggers. I would advise you to first read SQL SERVER – Interesting Observation of Logon Trigger On All Servers before continuing with this article further to have a complete idea of the subject.

The question I put forth in my previous article was – In single login why the trigger fires multiple times; it should be fired only once. I received numerous answers in thread as well as in my MVP private news group. Now, let us discuss the answer for the same.

The answer is – It happens because multiple SQL Server services are running as well as intellisense is turned on.

Let us verify the above answer.

First, run the following script to create database and logon Audit table.

/* Create Audit Database */
CREATE DATABASE AuditDb
GO
USE AuditDb
GO
/* Create Audit Table */
CREATE TABLE ServerLogonHistory
(SystemUser VARCHAR(512),
DBUser VARCHAR(512),
SPID INT,
LogonTime DATETIME)
GO
/* Create Logon Trigger */
CREATE TRIGGER Tr_ServerLogon
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO
AuditDb.dbo.ServerLogonHistory
SELECT SYSTEM_USER,USER,@@SPID,GETDATE()
END
GO

After that, disable all the other SQL Server Services as delineated in the image below using SQL Server Configuration Manager.

Next, try to logon to the system only one more time.

Check the audit table again to verify if there is a single entry for single login.

I would once again like to thank all of you for active participation and coming up with wonderful suggestions and answers. Let me have your opinion on this observation.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Interesting Observation of Logon Trigger On All Servers

I was recently working on security auditing for one of my clients. In this project, there was a requirement that all successful logins in the servers should be recorded. The solution for this requirement is a breeze! Just create logon triggers. I created logon trigger on server to catch all successful windows authentication as well SQL authenticated solutions. When I was done with this project, I made an interesting observation of executing logon trigger multiple times. It was absolutely unexpected for me! As I was logging only once, naturally, I was expecting the entry only once. However, it was doing it multiple times on different threads – indeed an eccentric phenomenon at first sight!

Let us first pore over our example.

Create database, table and logon trigger

/* Create Audit Database */
CREATE DATABASE AuditDb
GO
USE AuditDb
GO
/* Create Audit Table */
CREATE TABLE ServerLogonHistory
(SystemUser VARCHAR(512),
DBUser VARCHAR(512),
SPID INT,
LogonTime DATETIME)
GO
/* Create Logon Trigger */
CREATE TRIGGER Tr_ServerLogon
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO
AuditDb.dbo.ServerLogonHistory
SELECT SYSTEM_USER,USER,@@SPID,GETDATE()
END
GO

Login using SQL Authentication and check audit table


Login using Windows Authentication and check audit table

The above example clearly demonstrates that there are multiple entries in the Audit table. Also, on close observation it is evident that there are multiple process IDs as well. Based on these two observations I can come to one conclusion. Similar actions like login to the server took place multiple times.

Question to readers:

Have you ever experienced this kind of situation? If yes, then have you received similar entries?

For those, who have not experienced this phenomenon yet, they can recreate this situation very quickly by running the above script, and then you all can post your observations and conclusions here.

I am very much interested in learning the cause that triggers multiple entries. Valid suggestions and explanations will be published on this blog with due credit.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Guidelines and Coding Standards Complete List Download

SQL SERVER – Guidelines and Coding Standards complete List Download

Coding standards and guidelines are very important for any developer on the path of successful career. A coding standard is a set of guidelines, rules and regulations on how to write code. Coding standards should be flexible enough or should take care of the situation where they should not prevent best practices for coding. They are basically the guidelines that one should follow for better understanding.

The concept behind implementing coding standards and guidelines, is that the consistency and uniformity in programming so that if multiple people are working on the same code, it becomes easier to communicate, share with or understand each other’s work.

With the goal of promoting good coding standards and guidelines I have created document which can guide developers.

SQL SERVER – Guidelines and Coding Standards Part – 1

SQL SERVER – Guidelines and Coding Standards Part – 2

SQL SERVER – Guidelines and Coding Standards complete List Download

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – Interview Questions and Answers Complete List Download

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

Download SQL Server 2008 Interview Questions and Answers Complete List

UPDATE: This article series has been updated with new interview questions and answers series.

Interview is very important event for any person. A good interview leads to good career if candidate is willing to learn. I always enjoy interview questions and answers series. This is my very humble attempt to write SQL Server 2008 interview questions and answers. SQL Server is very large subject and not everything is usually asked in interview. In interview what matters the most is conceptual knowledge and learning attitude.

I have listed all the series in this post so that it can be easily downloaded and used. All the questions are collected and listed in one PDF which is here to download. If you have any question or if you want to add to any of the question please send me mail or write a comment.

SQL SERVER – 2008 – Interview Questions and Answers – Part 1

SQL SERVER – 2008 – Interview Questions and Answers – Part 2

SQL SERVER – 2008 – Interview Questions and Answers – Part 3

SQL SERVER – 2008 – Interview Questions and Answers – Part 4

SQL SERVER – 2008 – Interview Questions and Answers – Part 5

SQL SERVER – 2008 – Interview Questions and Answers – Part 6

SQL SERVER – 2008 – Interview Questions and Answers – Part 7

SQL SERVER – 2008 – Interview Questions and Answers – Part 8

Download SQL Server 2008 Interview Questions and Answers Complete List

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – Interview Questions and Answers – Part 1

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

SQL SERVER – 2008 – Interview Questions and Answers Complete List Download

1) General Questions of SQL SERVER

What is RDBMS?

Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage. (Read More Here)

What are the properties of the Relational tables?

Relational tables have six properties:

  • Values are atomic.
  • Column values are of the same kind.
  • Each row is unique.
  • The sequence of columns is insignificant.
  • The sequence of rows is insignificant.
  • Each column must have a unique name.

What is Normalization?

Database normalization is a data design and organization process applied to data structures based on rules that help building relational databases. In relational database design, the process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

What are different normalization forms?

1NF: Eliminate Repeating Groups

Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.

2NF: Eliminate Redundant Data

If an attribute depends on only part of a multi-valued key, remove it to a separate table.

3NF: Eliminate Columns Not Dependent On Key

If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key. (Read More Here)

BCNF: Boyce-Codd Normal Form

If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.

4NF: Isolate Independent Multiple Relationships

No table may contain two or more 1:n or n:m relationships that are not directly related.

5NF: Isolate Semantically Related Multiple Relationships

There may be practical constrains on information that justify separating logically related many-to-many relationships.

ONF: Optimal Normal Form

A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.

DKNF: Domain-Key Normal Form

A model free from all modification anomalies is said to be in DKNF.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

What is De-normalization?

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

What is Stored Procedure?

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

e.g. sp_helpdb, sp_renamedb, sp_depends etc.

What is Trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.

Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger. (Read More Here)

What is View?

A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

What is Index?

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes; they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.

What is a Linked Server?

Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server. (Read More Here)

© Copyright 2000-2009 Pinal Dave. All Rights Reserved. SQLAuthority.com

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Disable All the Trigger of Current Database

I have previously written article about SQL SERVER – Disable All Triggers on a Database – Disable All Triggers on All Servers. This is alternate method to achieve the same task.

Following article is sent by Manish Kaushik. I recommend all of you to read original article along with this article for complete idea.

CREATE PROCEDURE [dbo].[DisableAllTriggers]
AS
DECLARE
@string VARCHAR(8000)
DECLARE @tableName NVARCHAR(500)
DECLARE cur CURSOR
FOR SELECT
name AS tbname FROM sysobjects WHERE id IN(SELECT parent_obj FROM sysobjects WHERE xtype='tr')
OPEN cur
FETCH next FROM cur INTO @tableName
WHILE @@fetch_status = 0
BEGIN
SET
@string ='Alter table '+ @tableName + ' Disable trigger all'
EXEC (@string)
FETCH next FROM cur INTO @tableName
END
CLOSE
cur
DEALLOCATE cur
GO
----To execute the SP

EXEC [DisableAllTriggers]

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Best Articles on SQLAuthority.com

SQL SERVER – Cursor to Kill All Process in Database

SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored procedure

SQL SERVER – Shrinking Truncate Log File – Log Full

SQL SERVER – Simple Example of Cursor

SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case

SQL SERVER – Restore Database Backup using SQL Script (T-SQL)

SQL SERVER – T-SQL Script to find the CD key from Registry

SQL SERVER – Delete Duplicate Records – Rows

SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation

SQL SERVER – Union vs. Union All – Which is better for performance?

SQL SERVER – DBCC RESEED Table Identity Value – Reset Table Identity

SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

SQL SERVER – Difference between DISTINCT and GROUP BY – Distinct vs Group By

SQL SERVER – Index Seek Vs. Index Scan (Table Scan)

SQL SERVER – TempDB is Full. Move TempDB from one drive to another drive

SQL SERVER – T-SQL Paging Query Technique Comparison – SQL 2000 vs SQL 2005

SQL SERVER – Performance Optimization of SQL Query and FileGroups

SQL SERVER – Search Text Field – CHARINDEX vs PATINDEX

SQL SERVER – 2005 Explanation of TRY…CATCH and ERROR Handling

SQL SERVER – Script to find SQL Server on Network

SQL SERVER – Stored Procedures Advantages and Best Advantage

SQL SERVER – CASE Statement/Expression Examples and Explanation

SQL SERVER – Raid Configuration – RAID 10

SQL SERVER – Understanding new Index Type of SQL Server 2005 Included Column Index along with Clustered Index and Non-clustered Index

SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column value of the table

SQL SERVER – Six Properties of Relational Tables

SQL SERVER – TRIM() Function – UDF TRIM()

SQL SERVER – Difference between Unique Index vs Unique Constraint

SQL SERVER – 2005 Locking Hints and Examples

SQL SERVER – Good, Better and Best Programming Techniques

SQL SERVER – Random Number Generator Script – SQL Query

SQL SERVER – 2005 TOP Improvements/Enhancements

SQL SERVER – 2005/2000 Examples and Explanation for GOTO

SQL SERVER – Explanation SQL Commando GO

SQL SERVER – 2005 – List all the database

SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String

SQL SERVER – Disable Index – Enable Index – ALTER Index

SQL SERVER – 2005 – SSMS Change T-SQL Batch Separator

SQL SERVER – SQL Code Formatter Tools

SQL SERVER – 2005 Comparison EXCEPT operator vs. NOT IN

SQL SERVER – 2005 NorthWind Database or AdventureWorks Database – Samples Databases

SQL SERVER – 2005 Find Table without Clustered Index – Find Table with no Primary Key

SQL SERVER – 2005 Limiting Result Sets by Using TABLESAMPLE – Examples

SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility

SQL SERVER – 2005 Constraint on VARCHAR(MAX) Field To Limit It Certain Length

SQL SERVER Database Coding Standards and Guidelines Complete List Download

SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL

SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice

SQL SERVER – 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table

SQL SERVER – Easy Sequence of SELECT FROM JOIN WHERE GROUP BY HAVING ORDER BY

SQL SERVER – 2005 – UDF – User Defined Function to Strip HTML – Parse HTML – No Regular Expression

SQL SERVER – Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}

SQL SERVER – Explanation and Comparison of NULLIF and ISNULL

SQL SERVER – 2005 Row Overflow Data Explanation

SQL SERVER – Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005

SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable

SQL SERVER – Definition, Comparison and Difference between HAVING and WHERE Clause

SQL SERVER – 2005 Best Practices Analyzer Tutorial – Sample Example

SQL SERVER – 2005 – List All Stored Procedure Modified in Last N Days

SQL SERVER – Count Duplicate Records – Rows

SQL SERVER – CASE Statement in ORDER BY Clause – ORDER BY using Variable

SQL SERVER – Restore Database Without or With Backup – Everything About Restore and Backup

SQL SERVER – UDF – Function to Get Previous And Next Work Day – Exclude Saturday and Sunday

SQL SERVER – One Thing All DBA Must Know

SQL SERVER – 2005 – List Tables in Database Without Primary Key

SQL SERVER – 2005 – Find Stored Procedure Create Date and Modified Date

SQL SERVER – UDF – Validate Integer Function

SQL SERVER – What is SQL? How to pronounce SQL?

SQL SERVER – 2005 – Difference and Similarity Between NEWSEQUENTIALID() and NEWID()

SQL SERVER – 2005 – Explanation and Script for Online Index Operations – Create, Rebuild, Drop

SQL SERVER – Find Last Day of Any Month – Current Previous Next

SQL SERVER – T-SQL Script to Insert Carriage Return and New Line Feed in Code

SQL SERVER – 2005 – T-SQL Script to Attach and Detach Database

SQL SERVER – Actual Execution Plan vs. Estimated Execution Plan

SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text

SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database

SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database

SQL SERVER – 2005 – Introduction and Explanation to sqlcmd

SQL SERVER – UDF – User Defined Function – Get Number of Days in Month

SQL SERVER – 2005 – Start Stop Restart SQL Server From Command Prompt

SQL SERVER – 2005 – List All The Constraint of Database – Find Primary Key and Foreign Key Constraint in Database

SQL SERVER – UDF – Validate Positive Integer Function – Validate Natural Integer Function

SQL SERVER – Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT

SQL SERVER – 2005 – OUTPUT Clause Example and Explanation with INSERT, UPDATE, DELETE

SQL SERVER – 2005 – Sample Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE

SQL SERVER – Three T-SQL Script to Create Primary Keys on Table

Reference : Pinal Dave (http://blog.SQLAuthority.com)