Solved

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

Posted on 2008-10-22
3
675 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

823 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