On a related note, we have a project in development for our internal account managers and tech support team to be able to run any select query they want against our databases to help them resolve issues. The devloper asked me how she could go about making sure they didn't inadvertantly perform some query other than a select. My reply was to create a user account that had all privelages denied except for select and execute the procedure as that user account. Bear in mind that the AM's and tech support will be using a web app that is using an account whose only rights is to execute stored procedures.

This is the real code that was finally used, but is the initial test code I sent to the developer for testing how it would work. Bear in mind that this is SQL 2005 code.

Use

master

-- Create server login for test

Create

Login DBDataReaderOnly With Password = 'PeasPorridgeCold'

Use

DemoDatabase

-- Create database user for login

Create

User DBDataReader For Login DBDataReaderOnly With Default_Schema = dbo

-- Create databse role for easy assignment of permissions

Create

Role db_DataReaderOnly

-- Grant select rights

Grant

Select To db_DataReaderOnly

-- Deny all other permissions

Deny

Execute, Insert, Update, Delete, References, Alter, Take Ownership, View Definition To db_DataReaderOnly

-- Assign user to the database role

Exec

sp_addrolemember 'db_DataReaderOnly', 'DBDataReader'

Go

-- Create a test procedure

Create

Procedure dbo.USP_DynamicDataTest

@SQL

nvarchar(max)

With

Execute As 'DBDataReader'

As

Exec

sp_executesql @SQL

Go

-- Test new account, run each command individually

Exec

dbo.USP_DynamicDataTest @SQL = 'Select * From TestTable with(nolock)'

Exec

dbo.USP_DynamicDataTest @SQL = 'Delete Top (1) From TestTable'

Exec

dbo.USP_DynamicDataTest @SQL = 'Drop Table TestTable'