Solved

Accessing a table in stored procedure or table valued function using a different users rights

Posted on 2008-10-22
3
674 Views
Last Modified: 2009-11-17
Hi,

I am working on a project that completly run on SQL Server 2008. I have to write some stored procedures and table valued function with T-SQL or CLR for making some operations. In my database has several tables but users do not have right to access all of these tables. But users must access these tables to do these operations (For example inserting records to log table). DB administrator says that Log table can only be accessible from these stored procedure and table valued function for these users. Because some users added some illegal records to log table and change some other tables in the past. We want to control these user behaviors.

How can i do this
Thanks in advance
0
Comment
Question by:chelvaci
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
jorge_toriz earned 500 total points
ID: 22782647
If you give them the EXECUTE permission on your stored procedures then, with your stored procedure they will be able to modify the content of your tables
0
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 22782665
Test with this
USE master

GO

CREATE LOGIN testSecurity

WITH PASSWORD = '1234', CHECK_POLICY = 'OFF', CHECK_EXPIRATION = 'OFF'

GO

USE AdventureWorks

GO

CREATE USER testSecurity

FOR LOGIN testSecurity

GO

CREATE TABLE Test(

  Id INT

)

GO

CREATE PROC pInsertIntoTest(

   @Id INT

)

AS

BEGIN

  INSERT INTO Test VALUES (@Id)

END

GO

GRANT EXECUTE ON pInsertIntoTest TO testSecurity
 

--Now log on as testSecurity and test your stored procedure

EXEC pInsertIntoTest 10

--Try to read with your testSecurity user

SELECT * FROM Test

--Try to insert with your testSecurity user

INSERT INTO Test VALUES(11)

--In both cases you will get an error
 

--Now log on as the db_owner (or a user within db_datareader role) and then execute this

SELECT * FROM Test

--You will see your "10" value inserted

Open in new window

0
 

Author Comment

by:chelvaci
ID: 22782721
Thank you very much
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now