Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2008-10-22
3
Medium Priority
?
681 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 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.
Suggested Courses

580 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