Solved

Access ADP form not seemingly effected by SQL Server 2005 Security Settings

Posted on 2009-07-16
4
179 Views
Last Modified: 2013-12-05
Hello Experts,

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.

Dan


USE [Act]
GO
/****** Object:  StoredProcedure [dbo].[usp_DenyInsertDeleteUpdateTable]    Script Date: 07/16/2009 10:48:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[usp_DenyInsertDeleteUpdateTable] 
 
(
		@ARCHIVE_TABLE Char(100),
		@REVIEW_YEAR Char(4),
		@USER_NAME Char(50)
 )
 
AS
 
BEGIN
	-- 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 
 
END

Open in new window

0
Comment
Question by:DASILBE
  • 2
  • 2
4 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 24905541
Every time I hear someone mention the ACT! application/DB I shudder. As I understand it -- they really lock down the DB and what you can do with it.

There is a whole ACT! zone
Database --> Contact Management --> ACT
http://www.experts-exchange.com/Database/Contact_Management/ACT/

You can do a Request Attention and have the zones added. But from reading these questions, it sounds like there is no way to achieve what you are trying to do with the ADP. Maybe if you use the ACTReader as referred to in the second link you can get close.

Can't login to SQL 2005 using Mixed Mode Authentication
http://www.experts-exchange.com/Q_23805406.html

Enable sa login on existing sql 2005 database installation.
http://www.experts-exchange.com/SQL-Server-2005/Q_23382273.html
0
 

Author Comment

by:DASILBE
ID: 24907881
Hi Jimpen,

As it turns out, I found the solution.  The issue was the fact that my login on the server had sysadmin privledges and these privledges automatically override any object "Deny" settings that I was imposing.  The real issue is that when you use Windows login authentication for your SQL Server Security (Not mixed mode) then you have to consider the user login rights and what takes precedence.  I resolved this issue by creating another login that only had "public" as user role on the SQL Server itself and the Windows login.  The ADP actually integrates nicely with SQL Server.  I think most of us that experience this problem just don't realize how tightly SQL server database security is tied into Windows security and which rights have precedence.  The server apparently does.
0
 

Accepted Solution

by:
DASILBE earned 0 total points
ID: 24907925
I found the solution.  The issue was the fact that my login on the server had sysadmin privledges and these privledges automatically override any object "Deny" settings that I was imposing.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24908093
I'm glad you found an answer. If you click on the link Accept as a solution in your reply, it will close the question.

May all your days get brighter and brighter.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access 2016 - query 23 59
How Do I Select a Record using a Primary Key 3 28
A Function to parse a text string 4 34
deduplicating based on criteria 2 21
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question