?
Solved

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

Posted on 2008-10-22
3
Medium Priority
?
680 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
jorge_toriz earned 2000 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

765 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