Solved

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

Posted on 2008-10-22
3
673 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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you very much
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

771 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

10 Experts available now in Live!

Get 1:1 Help Now