Solved

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

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

738 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