I have successfully created a stored procedure which allows me to deny delete, insert and update privileges to a user in an SQL Server 2005 database called ACT. The procedure works fine and returns no errors when executed with the proper parameters. My problem is that although I am the creator of the database and the database owner, I want to make sure that my table is read only after my stored procedure executes. Even the owner or administrator should not be able to change the table unless another Grant is given. This procedure does not seem to be doing the job and it has no effect on the Access ADP file that is running as the front end for the database. I'm simply trying to make my table "Achievable" after the user decides to Lock the table by pressing a button on the front end. I'm not concerned with how to code this in Access, my concern is what needs to be done in SQL server to enable the change of security on the fly. Security for the SQL Server is based on login only. Thanks experts.
/****** Object: StoredProcedure [dbo].[usp_DenyInsertDeleteUpdateTable] Script Date: 07/16/2009 10:48:41 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [dbo].[usp_DenyInsertDeleteUpdateTable]
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @ARCHIVE_TABLE_NAME varchar(100)
DECLARE @SQL_STRING varchar(2000)
SET @ARCHIVE_TABLE_NAME = RTRIM(@ARCHIVE_TABLE) + @REVIEW_YEAR
SET @SQL_STRING = 'USE ACT; '
SET @SQL_STRING = @SQL_STRING + 'DENY DELETE, INSERT, UPDATE ON OBJECT::dbo.' + @ARCHIVE_TABLE_NAME + ' TO [' + @USER_NAME + '] '
EXECUTE usp_ExecuteSQL @SQL_STRING