SQL SERVER – Detecting guest User Permissions – guest User Access Status

Earlier I wrote the blog post SQL SERVER – Disable Guest Account – Serious Security Issue, and I got many comments asking questions related to the guest user.

Here are the comments of Manoj:

1) How do we know if the uest user is enabled or disabled?
2) What is the default for guest user in SQL Server?

Default settings for guest user

When SQL Server is installed by default, the guest user is disabled for security reasons. If the guest user is not properly configured, it can create a major security issue. You can read more about this here.

Identify guest user status

There are multiple ways to identify guest user status:

Using SQL Server Management Studio (SSMS)

You can expand the database node >> Security >> Users. If you see the RED arrow pointing downward, it means that the guest user is disabled.

Using sys.sysusers

Here is a simple script. If you notice column dbaccess as 1, it means that the guest user is enabled and has access to the database.

SELECT name, hasdbaccess
FROM sys.sysusers
WHERE name = 'guest'

Using sys.database_principals and sys.server_permissions

This script is valid in SQL Server 2005 and a later version. This is my default method recently.

SELECT name, permission_name, state_desc
FROM sys.database_principals dp
INNER JOIN sys.server_permissions sp
ON dp.principal_id = sp.grantee_principal_id
WHERE name = 'guest' AND permission_name = 'CONNECT'

Using sp_helprotect

Just run the following stored procedure which will give you all the permissions associated with the user.

sp_helprotect @username = 'guest'

Disable Guest Account

REVOKE CONNECT FROM guest

Additionally, the guest account cannot be disabled in master and tempdb; it is always enabled. There is a special need for this. Let me ask a question back at you:

In which scenario do you think this will be useful to keep the guest, and what will the additional configuration go along with the scenario?

Note: Special mention to Imran Mohammed for being always there when users need help.

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

About these ads

SQL SERVER – Force Removing User from Database – Fix: Error: Could not drop login ‘test’ as the user is currently logged in.

Yesterday I wrote a blog post discussing how guest user can become security threat. The script which was demonstrated for the example had a small T-SQL query which creates a new user. Later, I got an email from a user who had created this scenario on his production environment. It makes me sad that I had clearly talked multiple times about how to execute this as a trial on a development server or a test server, but NOT on a production server. Anyway, here is the email:

“Pinal,

I ran your test on our production server as our development server was busy. Now when I try to drop the test user, it gives me the following error:

Msg 15434, Level 16, State 1, Line 1
Could not drop login ‘test’ as the user is currently logged in.

Could you please help me drop this test user? I cannot restart the server as it is a production server, and I need to drop this user as fast as I can. First of all, I have closed all the connections but I do not know which connection is still open. I just need to identify which connection is open and kill it if required. In any case, I need to drop it before my supervisor catches me.”

This email I read really felt very awkward. Here is my answer. I hope you do not find it rude.

“Dear Friend,

First of all, I want to ask why you ran the demo on your production server. You must not execute it on a production server. It was just for the purpose of demonstration. This reminds me of an email I received from one of my friends that one should never post details which can harm a certain production server, as there are people who do not follow the advice no matter how many times you warn them.

Trust me – your supervisor will have configured audits and he will soon discover this test user, whether you tell him or not. I suggest that instead of hiding, you go and tell him everything in the most honest way you can.

Here is one more tip for you – killing any sessions without permission from a user or a senior admin will get you in trouble.

Once again I do not want your server to be exposed to potential security threats while your supervisor is away, so follow the steps mentioned below to drop the test user:

Find an active connection using the test user:

SELECT session_id
FROM sys.dm_exec_sessions
WHERE login_name = 'test'

Kill the active connection using the test user:

KILL 52 -- Replace 52 with the your session ids received from earlier query

Drop the test user:

DROP LOGIN test

Hopefully you’d keep your production server away from executing scripts meant to run on a development server.”

Well, after writing the email I did not feel good as I did not like to sound rude. However, I kept telling myself that it was needed. What do you think about this whole conversation?

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

SQL SERVER – Disable Guest Account – Serious Security Issue

“No Guests PLEASE!”

“Doesn’t your Indian tradition suggest welcoming guests and treating them in the best way possible?”

“Yes, but I am talking about the Guest user in SQL Server.”

Oh!”

This was a real conversation that happened a couple of years ago. I welcome guests as much as any other Indian does; however, I am strongly opinionated about guest user in SQL Server. I like to keep it disabled unless there is a special need of it. If there is some persistent need of a guest user, I suggest to create separate account. Again, there are always special cases where there is a need of this guest account. But in other cases, a guest account is not necessary.

A Real World Story

Today I am in the mood to tell a real life story.

About 2 years ago, I got a call very late in the evening from one of my friends. He said that his database was compromised and so he needed to talk to me. He described the situation to me – that his database had a single user (himself), but without his knowledge, his database was often observing non-permitted activities. He was very scared.

When there is no real clue, most companies take the database down or turn it offline to prevent any further damage to the system.

Just like the same, he decided to disconnect the database from other applications, as well as to move th behind firewall of the system. After making sure that there is no other application connected even at behind the firewall, there was still this inappropriate activity going on. He became more worried, and finally he sought my help.

I decided to take up the challenge to help him. After a while, we discovered that he had the GUEST user enable on his database. Even though he had already removed all the other accounts, he missed to remove the guest user account. As the guest user was enabled, any user who had permission to server was able to get into his database. Well, after a careful review, we realize that one of his co-workers was pulling the prank on him. He had given additional permission to his guest user which leads any user logging into server got admin rights to my friend’s database.

Upon figuring out this issue, we disabled the guest account and the problem was resolved. To complete the story, the guy who played the prank on my friend was moved to another department (almost as good as fired) where he could not touch the production database.

Reproduction of the scenario

After remembering the story, I decided to recreate the scenario here. Please note: do not play with Guest Account without fully understanding its implications.

USE AdventureWorks
GO
-- Note: NEVER run this on production server
-- Enabling guest account
GRANT CONNECT TO guest
GO
ALTER ROLE [db_owner] ADD MEMBER [guest]
GO
-- create test login
USE MASTER
GO
CREATE LOGIN [test] WITH PASSWORD=N'Complex@1212';
GO

When you explore the logins of the AdventureWorks database, you will not see ‘test’ as a user. Now let us login into SQL Server using ‘test’ user. When user will login using ‘test’ user he will be logged in as guest user in AdventureWorks database. Every user who login to AdventureWorks as guest user it will have admin access to the database.

This was the precise situation my friend had and he was not able to understand where things went wrong. As I said earlier, unless there is special case, guest user should be disabled.

Run following script to drop ‘test’ account which we created.

USE MASTER
GO
DROP LOGIN [test]
GO

Script to disable the guest account

REVOKE CONNECT FROM guest
GO

Well, share your stories in comments area. Additionally, have you ever prank your friend, if yes, I want to know. Additionally, for master, msdb and tempdb database guest user is required for SQL Server. If you enable guest database on modeldb, all the subsequent created database will have it enable, which is serious security issue.

NOTE: Do not try any of the script on production server or live environment. These scripts are for better understanding of the subject.

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

SQL SERVER – Case Sensitive Database and Database User – Fix: Error: 15151 – Cannot find the user , because it does not exist or you do not have permission.

Jeff asked me another question!

If you do not know Jeff, you may read the following blog posts. You will get the idea of Jeff’s personality and who Jeff really is.

This time, he sent me a screenshot. He was facing a very strange error. As his screenshot had confidential details, I created my own images which exactly simulate his issue for demonstration’s sake. Here are the partial details of his email. Please note that I have replaced his username and database name to protect his private information.

“Hi Pinal,

I think I am having one of those moments when I am feeling stupid or SQL Server is having fun with me.

Here is the scenario. I have two databases: AdventureWorks and MyAdventureWorks. I have a user called SQLAuthority in both  databases. Now when I try to grand access the user in one database it works fine, but when I try to do the same thing in another database, it gives me error. I am stunned as both the users are same. As a matter of fact, I had just created those users recently, and now I am not able to gain necessary permission.

Here is the screenshot where everything works fine:

Here is the screenshot that shows where I am getting crazy and gives following error:

Msg 15151, Level 16, State 1, Line 2
Cannot find the user ‘sqlauthority’, because it does not exist or you do not have permission.

Help me again. Why is this happening? I do not get it. HELP!”

That’s a very interesting question from him. For a moment, I was speechless as it is difficult for me to solve the problem for him remotely. After a while, I noticed that in his example, the case of the username did not match. In SSMS the username was SQLAuthority and in his T-SQL script it was sqlauthority. I told him to change the case of the T-SQL to match with the case of SSMS, and it right away solved his problem.

I asked him to run the following script which validated that his database was throwing an error because of case sensitivity. Read here how you can identify collation of any database: SQL SERVER – 2005 – Find Database Collation Using T-SQL and SSMS

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') AdventureWorks;
SELECT DATABASEPROPERTYEX('MyAdventureWorks', 'Collation') MyAdventureWorks;

The above query returns the following results:

Learning: Case sensitivity of a database does not only matter in the data of the table, but also in objects like tablename, columnname, and even username. If you know any other situation like this, please share your knowledge in the comments area.

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

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)

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)