SQL SERVER – 2012 Auditing Enhancement – On Audit Log Failure Options – Maximum Rollover Files

Recently I was exploring SQL Server Audit and found something very interesting. I found two enhancements in the SQL Server 2008 Create Audit Screen.

SQL Server 2012 Create Audit Screen

SQL Server 2008 Create Audit Screen

On Audit Log Failure Options

You can see that in SQL Server 2012 they have added two more options for audit log failure. In earlier version the only option was to shut down the server when there was audit log failure. Now you can fail the operation as well continue on log failure. This new options now give finer control on the behavior of the audit failure scenario.When target is not available due to any reason and audit cannot log the event, it can be now continued, in another word the audit continues to attempt to log events and will resume if the failure condition is resolved. This is very important feature because earlier when Audio was failing the option which we had was to shutdown the server. There were the cases when shutting down the server is not the good option but continuing the business operation is the priority, this option should be exercised. Additionally, note that the new default value is now CONTINUE. User has option to select Fail Operation where the Audit will not attempt when the target is available or continuing auditing is possible.

Maximum Rollover Files

Earlier, there were two options – have infinite number of log files or roll over the files after fixed number. Now in SQL Server 2012 there is option to keep the fixed number of the files along with no roll-over. This gives additional control to user when they want to save every single data and do not want to lose any critical information due to rollover.

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

About these ads

SQL SERVER – ‘Denali’ – A Simple Example of Contained Databases

Recently I was asked with the question: What is new for Database Security in SQL Server “Denali”?

I think this is a very interesting question as I always wanted to talk about Contained Database, and this question gives me the chance to do so. Let us start with discussing contained database.

A Contained Database is a database which contains all the necessary settings and metadata, making database easily portable to another server. This database will contain all the necessary details and will not have to depend on any server where it is installed for anything. You can take this database and move it to another server without having any worries.

The real question is, “What about users who are connecting to this database?” Once the contained database is moved, the users are moved as well, and users who belong to the contained database will have no access outside the contained database.

In summary, “Database is now self-contained. Database which is ’contained’ will not depend on anything on the server where it is installed.”

Let us try out this feature on SQL Server Denali. We will do the following steps:

  1. Enable Contained Database
  2. Create Contained Database
  3. Create User in Contained Database
  4. Try if the user can access outside Contained Database

We can do various tests on this subject; however, in this blog post we will limit out exercise to the above four points.

Enable Contained Database

Run the following code on SQL Server Denali. This code will enable the settings for the contained database.

sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'contained database authentication', 1
GO
RECONFIGURE WITH OVERRIDE
GO

Create Contained Database

CREATE DATABASE [ContainedDatabase]
CONTAINMENT
= PARTIAL
ON PRIMARY
( NAME = N'ContainedDatabase', FILENAME = N'C:\ContainedDatabase.mdf')
LOG ON
( NAME = N'ContainedDatabase_log', FILENAME = N'C:\ContainedDatabase_log.ldf')
GO

Create User in Contained Database

USE [ContainedDatabase]
GO
CREATE USER ContainedUser
WITH PASSWORD = 'pass@word';
GO

Try if this user can access out side Contained Database

To test this, we will attempt to login in the database with default settings (where login database is the master).

When we attempt this, we will be not able to login in the server simply because the user does not exist at the server level.

Now, let us try to login in the system using the username which was created in the Contained Database.

You will notice that the login would be successful in the server. When expanded it, the user will have access to the contained database only, and not to any other database.

We will tackle more about this interesting subject in the future.

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

SQL SERVER – Securing TRUNCATE Permissions in SQL Server

Download the Script of this article from here.

On December 11, 2010, Vinod Kumar, a Databases & BI technology evangelist from Microsoft Corporation, graced Ahmedabad by spending some time with the Community during the Community Tech Days (CTD) event. As he was running through a few demos, Vinod asked the audience one of the most fundamental and common interview questions – “What is the difference between a DELETE and TRUNCATE?

Ahmedabad SQL Server User Group Expert Nakul Vachhrajani has come up with excellent solutions of the same. I must congratulate Nakul for this excellent solution and as a encouragement to User Group member, I am publishing the same article over here.

Nakul Vachhrajani is a Software Specialist and systems development professional with Patni Computer Systems Limited. He has functional experience spanning legacy code deprecation, system design, documentation, development, implementation, testing, maintenance and support of complex systems, providing business intelligence solutions, database administration, performance tuning, optimization, product management, release engineering, process definition and implementation. He has comprehensive grasp on Database Administration, Development and Implementation with MS SQL Server and C, C++, Visual C++/C#. He has about 6 years of total experience in information technology. Nakul is an member of the Ahmedabad and Gandhinagar SQL Server User Groups, and actively contributes to the community by actively participating in multiple forums and websites like SQLAuthority.com, BeyondRelational.com, SQLServerCentral.com and many others.

Please note: The opinions expressed herein are Nakul own personal opinions and do not represent his employer’s view in anyway.

All data from everywhere here on Earth go through a series of  four distinct operations, identified by the words: CREATE, READ, UPDATE and DELETE, or simply, CRUD. Putting in Microsoft SQL Server terms, is the process goes like this: INSERT, SELECT, UPDATE and DELETE/TRUNCATE.

Quite a few interesting responses were received and evaluated live during the session. To summarize them, the most important similarity that came out was that both DELETE and TRUNCATE participate in transactions. The major differences (not all) that came out of the exercise were:

DELETE:

DELETE supports a WHERE clause
DELETE removes rows from a table, row-by-row
Because DELETE moves row-by-row, it acquires a row-level lock
Depending upon the recovery model of the database, DELETE is a fully-logged operation.
Because DELETE moves row-by-row, it can fire off triggers

TRUNCATE:

TRUNCATE does not support a WHERE clause
TRUNCATE works by directly removing the individual data pages of a table
TRUNCATE directly occupies a table-level lock.
(Because a lock is acquired, and because TRUNCATE can also participate in a transaction, it has to be a logged operation)
TRUNCATE is, therefore, a minimally-logged operation; again, this depends upon the recovery model of the database
Triggers are not fired when TRUNCATE is used (because individual row deletions are not logged)

Finally, Vinod popped the big homework question that must be critically analyzed:

“We know that we can restrict a DELETE operation to a particular user, but how can we restrict the TRUNCATE operation to a particular user?”

After returning home and having a nice cup of coffee, I noticed that my gray cells immediately started to work. Below was the result of my research.

As what is always said, the devil is in the details. Upon looking at the Permissions section for the TRUNCATE statement in Books On Line, the following jumps right out:

The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.

Now, what does this mean? Unlike DELETE, one cannot directly assign permissions to a user/set of users allowing or revoking TRUNCATE rights. However, there is a way to circumvent this. It is important to recall that in Microsoft SQL Server, database engine security surrounds the concept of a “securable”, which is any object like a table, stored procedure, trigger, etc. Rights are assigned to a principal on a securable. Refer to the image below (taken from the SQL Server Books On Line).

urable”, which is any object like a table, stored procedure, trigger, etc. Rights are assigned to a principal on a securable. Refer to the image below (taken from the SQL Server Books On Line).

SETTING UP THE ENVIRONMENT – (01A_Truncate Table Permissions.sql) Script Provided at the end of the article.

By the end of this demo, one will be able to do all the CRUD operations, except the TRUNCATE, and the other will only be able to execute the TRUNCATE.

All you will need for this test is any edition of SQL Server 2008. (With minor changes, these scripts can be made to work with SQL 2005.)

We begin by creating the following:

1.       A test database

2.        Two database roles: associated logins and users

3.       Switch over to the test database and create a test table. Then, add some data into it. I am using row constructors, which is new to SQL 2008.

Creating the modules that will be used to enforce permissions

1.       We have already created one of the modules that we will be assigning permissions to. That module is the table: TruncatePermissionsTest

2.       We will now create two stored procedures; one is for the DELETE operation and the other for the TRUNCATE operation. Please note that for all practical purposes, the end result is the same – all data from the table TruncatePermissionsTest is removed

Assigning the permissions

Now comes the most important part of the demonstration – assigning permissions. A permissions matrix can be worked out as under:

To apply the security rights, we use the GRANT and DENY clauses, as under:

That’s it! We are now ready for our big test!

THE TEST (01B_Truncate Table Test Queries.sql) Script Provided at the end of the article.

I will now need two separate SSMS connections, one with the login AllowedTruncate and the other with the login RestrictedTruncate.

Running the test is simple; all that’s required is to run through the script – 01B_Truncate Table Test Queries.sql. What I will demonstrate here via screen-shots is the behavior of SQL Server when logged in as the AllowedTruncate user. There are a few other combinations than what are highlighted here. I will leave the reader the right to explore the behavior of the RestrictedTruncate user and these additional scenarios, as a form of self-study.

1.       Testing SELECT permissions

2.       Testing TRUNCATE permissions (Remember, “deny by default”?)

3.       Trying to circumvent security by trying to TRUNCATE the table using the stored procedure

Hence, we have now proved that a user can indeed be assigned permissions to specifically assign TRUNCATE permissions. I also hope that the above has sparked curiosity towards putting some security around the probably “destructive” operations of DELETE and TRUNCATE.

I would like to wish each and every one of the readers a very happy and secure time with Microsoft SQL Server.

(Please find the scripts – 01A_Truncate Table Permissions.sql and 01B_Truncate Table Test Queries.sql that have been used in this demonstration. Please note that these scripts contain purely test-level code only. These scripts must not, at any cost, be used in the reader’s production environments).

01A_Truncate Table Permissions.sql

/*
*****************************************************************************************************************
Developed By          : Nakul Vachhrajani
Functionality         : This demo is focused on how to allow only TRUNCATE permissions to a particular user
How to Use            : 1. Run through, step-by-step through the sequence till Step 08 to create a test database
2. Switch over to the "Truncate Table Test Queries.sql" and execute it step-by-step
in two different SSMS windows, one where you have logged in as 'RestrictedTruncate',
and the other as 'AllowedTruncate'
3. Come back to "Truncate Table Permissions.sql"
4. Execute Step 10 to cleanup!
Modifications         :
December 13, 2010 - NAV - Updated to add a security matrix and improve code readability when applying security
December 12, 2010 - NAV - Created
*****************************************************************************************************************
*/
-- Step 01: Create a new test database
CREATE DATABASE TruncateTestDB
GO
USE TruncateTestDB
GO
-- Step 02: Add roles and users to demonstrate the security of the Truncate operation
-- 2a. Create the new roles
CREATE ROLE AllowedTruncateRole;
GO
CREATE ROLE RestrictedTruncateRole;
GO
-- 2b. Create new logins
CREATE LOGIN AllowedTruncate WITH PASSWORD = 'truncate@2010',
CHECK_POLICY = ON
GO
CREATE LOGIN RestrictedTruncate WITH PASSWORD = 'truncate@2010',
CHECK_POLICY = ON
GO
-- 2c. Create new Users using the roles and logins created aboave
CREATE USER TruncateUser FOR LOGIN AllowedTruncate WITH DEFAULT_SCHEMA = dbo
GO
CREATE USER NoTruncateUser FOR LOGIN RestrictedTruncate WITH DEFAULT_SCHEMA = dbo
GO
-- 2d. Add the newly created login to the newly created role
sp_addrolemember 'AllowedTruncateRole','TruncateUser'
GO
sp_addrolemember 'RestrictedTruncateRole','NoTruncateUser'
GO
-- Step 03: Change over to the test database
USE TruncateTestDB
GO
-- Step 04: Create a test table within the test databse
CREATE TABLE TruncatePermissionsTest (Id INT IDENTITY(1,1),
Name NVARCHAR(50))
GO
-- Step 05: Populate the required data
INSERT INTO TruncatePermissionsTest VALUES (N'Delhi'),
(
N'Mumbai'),
(
N'Ahmedabad')
GO
-- Step 06: Encapsulate the DELETE within another module
CREATE PROCEDURE proc_DeleteMyTable
WITH EXECUTE AS SELF
AS DELETE FROM TruncateTestDB..TruncatePermissionsTest
GO
-- Step 07: Encapsulate the TRUNCATE within another module
CREATE PROCEDURE proc_TruncateMyTable
WITH EXECUTE AS SELF
AS TRUNCATE TABLE TruncateTestDB..TruncatePermissionsTest
GO
-- Step 08: Apply Security
/*
*****************************SECURITY MATRIX***************************************
===================================================================================
Object                   | Permissions |                 Login
|             | AllowedTruncate   |   RestrictedTruncate
|             |User:NoTruncateUser|   User:TruncateUser
===================================================================================
TruncatePermissionsTest  | SELECT,     |      GRANT        |      (Default)
| INSERT,     |                   |
| UPDATE,     |                   |
| DELETE      |                   |
-------------------------+-------------+-------------------+-----------------------
TruncatePermissionsTest  | ALTER       |      DENY         |      (Default)
-------------------------+-------------+----*/----------------+-----------------------
proc_DeleteMyTable | EXECUTE | GRANT | DENY
-------------------------+-------------+-------------------+-----------------------
proc_TruncateMyTable | EXECUTE | DENY | GRANT
-------------------------+-------------+-------------------+-----------------------
*****************************SECURITY MATRIX***************************************
*/
/* Table: TruncatePermissionsTest*/
GRANT SELECT, INSERT, UPDATE, DELETE ON TruncateTestDB..TruncatePermissionsTest
TO NoTruncateUser
GO
DENY ALTER ON TruncateTestDB..TruncatePermissionsTest
TO NoTruncateUser
GO
/* Procedure: proc_DeleteMyTable*/
GRANT EXECUTE ON TruncateTestDB..proc_DeleteMyTable
TO NoTruncateUser
GO
DENY EXECUTE ON TruncateTestDB..proc_DeleteMyTable
TO TruncateUser
GO
/* Procedure: proc_TruncateMyTable*/
DENY EXECUTE ON TruncateTestDB..proc_TruncateMyTable
TO NoTruncateUser
GO
GRANT EXECUTE ON TruncateTestDB..proc_TruncateMyTable
TO TruncateUser
GO
-- Step 09: Test
--Switch over to the "Truncate Table Test Queries.sql" and execute it step-by-step in two different SSMS windows:
--    1. one where you have logged in as 'RestrictedTruncate', and
--    2. the other as 'AllowedTruncate'
-- Step 10: Cleanup
sp_droprolemember 'AllowedTruncateRole','TruncateUser'
GO
sp_droprolemember 'RestrictedTruncateRole','NoTruncateUser'
GO
DROP USER TruncateUser
GO
DROP USER NoTruncateUser
GO
DROP LOGIN AllowedTruncate
GO
DROP LOGIN RestrictedTruncate
GO
DROP ROLE AllowedTruncateRole
GO
DROP ROLE RestrictedTruncateRole
GO
USE MASTER
GO
DROP DATABASE TruncateTestDB
GO

01B_Truncate Table Test Queries.sql

/*
*****************************************************************************************************************
Developed By          : Nakul Vachhrajani
Functionality         : This demo is focused on how to allow only TRUNCATE permissions to a particular user
How to Use            : 1. Switch over to this from "Truncate Table Permissions.sql", Step #09
2. Execute this step-by-step in two different SSMS windows
a. One where you have logged in as 'RestrictedTruncate', and
b. The other as 'AllowedTruncate'
3. Return back to "Truncate Table Permissions.sql"
4. Execute Step 10 to cleanup!
Modifications         :
December 12, 2010 - NAV - Created
*****************************************************************************************************************
*/
-- Step 09A: Switch to the test database
USE TruncateTestDB
GO
-- Step 09B: Ensure that we have valid data
SELECT * FROM TruncatePermissionsTest
GO
-- (Expected: Following error will occur if logged in as "AllowedTruncate")
-- Msg 229, Level 14, State 5, Line 1
-- The SELECT permission was denied on the object 'TruncatePermissionsTest', database 'TruncateTestDB', schema 'dbo'.
--Step 09C: Attempt to Truncate Data from the table without using the stored procedure
TRUNCATE TABLE TruncatePermissionsTest
GO
-- (Expected: Following error will occur)
--  Msg 1088, Level 16, State 7, Line 2
--  Cannot find the object "TruncatePermissionsTest" because it does not exist or you do not have permissions.
-- Step 09D:Regenerate Test Data
INSERT INTO TruncatePermissionsTest VALUES (N'London'),
(
N'Paris'),
(
N'Berlin')
GO
-- (Expected: Following error will occur if logged in as "AllowedTruncate")
-- Msg 229, Level 14, State 5, Line 1
-- The INSERT permission was denied on the object 'TruncatePermissionsTest', database 'TruncateTestDB', schema 'dbo'.
--Step 09E: Attempt to Truncate Data from the table using the stored procedure
EXEC proc_TruncateMyTable
GO
-- (Expected: Will execute successfully with 'AllowedTruncate' user, will error out as under with 'RestrictedTruncate')
-- Msg 229, Level 14, State 5, Procedure proc_TruncateMyTable, Line 1
-- The EXECUTE permission was denied on the object 'proc_TruncateMyTable', database 'TruncateTestDB', schema 'dbo'.
-- Step 09F:Regenerate Test Data
INSERT INTO TruncatePermissionsTest VALUES (N'Madrid'),
(
N'Rome'),
(
N'Athens')
GO
--Step 09G: Attempt to Delete Data from the table without using the stored procedure
DELETE FROM TruncatePermissionsTest
GO
-- (Expected: Following error will occur if logged in as "AllowedTruncate")
-- Msg 229, Level 14, State 5, Line 2
-- The DELETE permission was denied on the object 'TruncatePermissionsTest', database 'TruncateTestDB', schema 'dbo'.
-- Step 09H:Regenerate Test Data
INSERT INTO TruncatePermissionsTest VALUES (N'Spain'),
(
N'Italy'),
(
N'Greece')
GO
--Step 09I: Attempt to Delete Data from the table using the stored procedure
EXEC proc_DeleteMyTable
GO
-- (Expected: Following error will occur if logged in as "AllowedTruncate")
-- Msg 229, Level 14, State 5, Procedure proc_DeleteMyTable, Line 1
-- The EXECUTE permission was denied on the object 'proc_DeleteMyTable', database 'TruncateTestDB', schema 'dbo'.
--Step 09J: Close this SSMS window and return back to "Truncate Table Permissions.sql"

Thank you Nakul to take up the challenge and prove that Ahmedabad and Gandhinagar SQL Server User Group has talent to solve difficult problems.

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

SQL SERVER – Best Practices for DBA Before Taking Vacation

This blog post is written in response to T-SQL Tuesday hosted by Jason Brimhall.

Everybody wants to take a vacation. Who does not love vacation, anyway? However, it seems that it has been getting more and more difficult to take vacation recently. There are two reasons why a person is not able to enjoy his vacation. First is due to company policies (bad boss!), and second is your responsibilities. Well, I cannot guide you much about company policy issues simply because I cannot do something about it. I have a wonderful boss and I have been taking many vacations, doing a few things whenever I’m on vacations.

Do not think about your Job!

This is the most difficult task. A very usual scenario when one goes to vacation is that he continuously thinks about his job even though he is actually on vacation. Here are a few reasons regarding this dilemma:

  • He thinks that without him, the office would not be able to function.
  • He thinks that without him, someone else would do better on the job and so he would lose the title “the Superhero”.
  • He would miss the gossips at work.
  • He is too comfortable in his job, and doing anything else scares him, even taking vacation.

Relax! None of the above would happen. Organizations always function with or without you. It is not about you; in fact, it is never about you. If you take a vacation, stop thinking about work and everything else. Just think of how you would enjoy your vacation.

Handling Job responsibilities

If you are a DBA, you’ll notice that sometimes it is very difficult to go out on vacation because there are so many critical tasks you are handling and you simply cannot leave them for vacation. I have faced this situation many times. I remember that time when I was on vacation; the Jr. Guy who is supposed to take over my job responsibility ended up calling me with issues or problems. In IT, we all know that we are not always able to talk on the phone and solve the problems. In fact, I always have to see the screen when I am advising or talking technical subject.

After working for many years, I have finally figured out how to avoid this kind of situation. Here are things I like to do regarding this problem:

No Super Hero!

I never try to become the only man in the organization who knows everything because it does not work out that well. Job security is good, but overdoing things is not good as well. There are many problems that arise with a certain situation. I always teach the critical task to another person in my organization. I make sure that he knows it very well. In fact, even though I am present in the organization, I often ask him to take care of the situation for me. This way, I make sure there’s a person besides me who knows how to handle the critical task. Now, I do not worry about this at all when I am on vacation.

Automation!

Well, we are working on this actively. There are so many different tasks which can be automated, yet we have ignored all these years. Many times, the argument is very simple. For example, a machine is still a machine so we need to at least take a look before approving final changes. All of these are true but there is something more than that for sure. We do not want to go back to “start”,  as described in the futuristic movies like I, Robot. However, there are a few things we can automate and leave with the machine. When I automate any task which I used to do manually, I make sure that I check the data being passed to or from me. I make sure that the data passed is proper and valid, and there are enough checkpoints in the process. Although I double-check these checkpoints, I do promote automation. We are currently looking into Powershell to automate a few tasks. I will post a detailed article about this subject when we are done with examining it.

Backup and Restore!

This task is very straightforward. Before I go on vacation, I always take one full backup of all the settings, jobs, and database. I keep them in a safe location and inform the next person in-charge the where I placed them. I was once asked to return from vacation earlier because something very bad happened and no one remembered what the original setting of the system was before it went down. This kind of the note can be useful during such cases.

Document, and Document it Again!

Well, let us assume you have explained everything to the next person in-charge and he has understood everything you wanted to explain. Then you went to vacation and something bad really happened, and he forgot what you have explained. Well this happens and, in fact, the guy who claims he has a sharp memory faces this issue the most. I keep it very standard; I always write down all the procedures and send an email to the person in-charge. There is a good probability that he will not remember your instructions, but he will surely remember when he reads your email or document.

Keep the phone on!

I think this can be an issue when one goes for vacation. I have seen many of my friends turning off their phones or pagers when they go on a vacation. Their argument is: “Well, it is vacation and this how it’s supposed to be.” However, I do the absolute opposite. I like to keep my phone on. This way, you will still know what happens when you’re not at work, plus, you would know a bit of what’s happening in your friends’ and relatives’ lives. This is the same thing for me. When I am on vacation, I like to keep things updated with what’s going on at work. I check my work email once a day and keep my phone on in case of emergency. I politely say “No” when I think I do not want to attend something and so far, I have never faced any problem with that. My co-worker and boss is very nice to me and I have never received any interruption in my vacation, unless it is so urgent that someone is dying.

There are many things to do on this subject, but these are what I usually do.

Let me know your idea about going on vacation!

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

SQLAuthority News – Wireless Router Security and Attached Devices – Complex Password

In the last week, I have received calls from friends who told me that they have got strange emails from me. To my surprise, I did not send them any emails. I was not worried until my wife complained that she was not able to find one of the very important folders containing our daughter’s photo that is located in our shared drive. This was alarming in my par, so I started a search around my computer’s folders. Again, please note that I am by no means a security expert. I checked my entire computer with virus and spyware, and strangely, there I found nothing. I tried to think what can cause this happening. I suddenly realized that there was a power outage in my area for about two hours during the days I have mentioned. Back then, my wireless router needed to be reset, and so I did. I had set up my WPA-PSK [TKIP] + WPA2-PSK [AES] very well. My key was very simple ( ‘SQLAuthority1′), and I never thought of changing it. (It is now replaced with a very complex one).

While checking the Attached Devices, I found out that there was another very strange computer name and IP attached to my network. And so as soon as I found out that there is strange device attached to my computer, I shutdown my local network. Afterwards, I reconfigured my wireless router with a more complex security key. Since I created the complex password, I noticed that the user is no more connecting to my machine.

Subsequently, I figured out that I can also set up Access Control List. I added my networked computer to that list as well. When I tried to connect from an external laptop which was not in the list but with a valid security key, I was not able to access the network, neither able to connect to it. I wasn’t also able to connect using a remote desktop, so I think it was good.

If you have received any nasty emails from me (from my gmail account) during the afore-mentioned days, I want to apologize. I am already paying for my negligence of not putting a complex password; by way of losing the important photos of my daughter. I have already checked with my client, whose password I saved in SSMS, so there was no issue at all. In fact, I have decided to never leave any saved password of production server in my SSMS. Here is the tip SQL SERVER – Clear Drop Down List of Recent Connection From SQL Server Management Studio to clean them.

I think after doing all this, I am feeling safe right now. However, I believe that safety is an illusion of many times. I need your help and advice if there is anymore I can do to stop unauthorized access.

I am seeking advice and help through your comments.

Update: Edited first line to remove dates.

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

SQL SERVER – Difference Between GRANT and WITH GRANT

What is the difference between GRANT and WITH GRANT when giving permissions to the user? This is a very interesting question recently asked me to during my session at TechMela Nepal.

Let us first see the syntax and analyze.

GRANT:
USE master;
GRANT VIEW ANY DATABASE TO username;
GO

WITH GRANT:
USE master;
GRANT VIEW ANY DATABASE TO username WITH GRANT OPTION;
GO

The difference between these options is very simple. In case of only GRANT, the username cannot grant the same permission to other users. On the other hand, with the option WITH GRANT, the username will be able to give the permission after receiving requests from other users.

This is a very basic definition of the subject. I would like to request my readers to come up with a working script to prove this scenario. Please submit your script to me by email (pinal ‘at’ sqlauthority.com), or in comment field.

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

SQL SERVER – Get the List of Object Dependencies – sp_depends and information_schema.routines and sys.dm_sql_referencing_entities

Recently, I read a question on my friend Ritesh Shah‘s SQL site regarding the following: sp_depends does not give appropriate results whereas information_schema.routines does give proper answer.

I have quite often seen that information_schema.routines gives proper dependency relationship where assp_depends returns an incorrect answer. However, as per book online sp_depends will be deprecated, and instead, sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities are recommended.

Let us quickly see where sp_depends fail and other solutions work fine.

Let us first create two scenarios.

Scenario 1: Normal Table Creation Order, where objects are created first and then used afterwords.

USE TempDB
GO
CREATE TABLE dbo.TestTable
( ID INT,
Name VARCHAR(100))
GO
-- dbo.First is not created yet
CREATE PROCEDURE dbo.Second
AS
EXEC
dbo.First
GO
CREATE PROCEDURE dbo.First
AS
SELECT
ID, Name
FROM TestTable
GO

Scenario 2: Objects are created afterwords and they are referenced first.

USE TempDB
GO
CREATE TABLE dbo.TestTable
( ID INT,
Name VARCHAR(100))
GO
CREATE PROCEDURE dbo.First
AS
SELECT
ID, Name
FROM TestTable
GO
-- dbo.First is already created
CREATE PROCEDURE dbo.Second
AS
EXEC
dbo.First
GO

Now let us run following three queries on both the scenarios.

-- Method 1: Using sp_depends
sp_depends 'dbo.First'
GO
-- Method 2: Using information_schema.routines
SELECT *
FROM information_schema.routines ISR
WHERE CHARINDEX('dbo.First', ISR.ROUTINE_DEFINITION) > 0
GO
-- Method 3: Using DMV sys.dm_sql_referencing_entities
SELECT referencing_schema_name, referencing_entity_name,
referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('dbo.First', 'OBJECT');
GO

Result from Scenario 1

Result from Scenario 2

It is clear that sp_depends does not give proper/correct results when the object creation order is different or following deferred name resolution.

I suggest the use of the third method, in which sys.dm_sql_referencing_entities is used.

Use the following script to get correct dependency:

SELECT referencing_schema_name, referencing_entity_name,
referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('YourObject', 'OBJECT');
GO

Let me know the type of scripts you use for finding Object Dependencies. I will post your script with due credit.

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