October 25, 2012 at 3:07 am
Hello All,
I have a scenario where i am looking to restrict a login only to read from the tables but the same login is used to execute stored procedures which are having some DML statements in it?
How to achieve this?At a high level i want to keep the login to only read from tables but at the same time it should allow the user to execute the stored procedures(Having DML statements).
Please let me know whether this is feasible.
Your help would be appreciated
October 25, 2012 at 3:17 am
Give the user select rights on the tables and execute rights on the procedures. It will work exactly as you want. They'll be able to only select directly from the tables, but when they run a proc whatever that proc does will work, providing the procedures don't use dynamic SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 25, 2012 at 3:27 am
another solution though they are not approprate , See the link http://www.mssqltips.com/sqlservertip/2711/different-ways-to-make-a-table-read-only-in-a-sql-server-database/
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 25, 2012 at 3:59 am
Thanks for your reply
The user has permissions to execute the stored procedure.The stored procdure is having CREATE,ALTER TABLE, ALTER PARTITIONS statements in it?
Iam just looking to restrict a login only to read from the tables but while executing the stored prcodure it must allow the user to do the above operations.Is this possible?
Your help would be highly appreciated
October 25, 2012 at 4:11 am
kk.86manu (10/25/2012)
Iam just looking to restrict a login only to read from the tables but while executing the stored prcodure it must allow the user to do the above operations.Is this possible?
This is what GAil explained above
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 25, 2012 at 4:57 am
Thanks for your reply
Unfortunately i am still not able to do this.Please see the details below
Scenario:
I have a login test_login which has role as db_datareader and db_datawriter.
Created this stored procedure with another login which had full access.The stored procedure has create table statement inside it
create proc test1
as
begin
create table test1
(id int)
end
I granted execution rights for the following object for test_login
GRANT EXEC ON test1 TO test_login
When i execute this SP with test_login .i get the error 'CREATE TABLE permission denied in database'.
I want this SP to create the table.Is this possible in current security context?
Please correct me if iam wrong.
October 25, 2012 at 4:58 am
The user has permissions to execute the stored procedure.The stored procdure is having CREATE,ALTER TABLE, ALTER PARTITIONS statements in it?
Just to clarify, these are DDL statements and not DML.
-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
October 25, 2012 at 4:59 am
For DDL you may need to use EXECUTE AS in the procedure definition. Your original post asked about DML.
Just... why procedures that alter the DB structure?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 25, 2012 at 5:09 am
kk.86manu (10/25/2012)
Thanks for your replyUnfortunately i am still not able to do this.Please see the details below
Scenario:
I have a login test_login which has role as db_datareader and db_datawriter.
Created this stored procedure with another login which had full access.The stored procedure has create table statement inside it
create proc test1
as
begin
create table test1
(id int)
end
I granted execution rights for the following object for test_login
GRANT EXEC ON test1 TO test_login
When i execute this SP with test_login .i get the error 'CREATE TABLE permission denied in database'.
I want this SP to create the table.Is this possible in current security context?
Please correct me if iam wrong.
If the owner of the procedure has the rights to create table / issue DDL statements, then you could edit your procedure like so:
create proc test1
WITH EXECUTE AS OWNER
as
begin
create table test1
(id int)
end
Otherwise you could use a specific SQL User that has these rights:
WITH EXECUTE AS 'UserName'
Edit: Didn't realize Gail already answered, damn you browser refresh!
-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply