Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Login restriction Expand / Collapse
Author
Message
Posted Thursday, October 25, 2012 3:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:47 AM
Points: 50, Visits: 222
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
Post #1376849
Posted Thursday, October 25, 2012 3:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1376856
Posted Thursday, October 25, 2012 3:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1376864
Posted Thursday, October 25, 2012 3:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:47 AM
Points: 50, Visits: 222
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
Post #1376877
Posted Thursday, October 25, 2012 4:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1376884
Posted Thursday, October 25, 2012 4:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:47 AM
Points: 50, Visits: 222
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.

Post #1376912
Posted Thursday, October 25, 2012 4:58 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 28, 2014 1:50 PM
Points: 91, Visits: 164,496
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.

Difference here


-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers.

I believe in Codd
... and Thinknook is my Chamber of Understanding
Post #1376914
Posted Thursday, October 25, 2012 4:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1376915
Posted Thursday, October 25, 2012 5:09 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 28, 2014 1:50 PM
Points: 91, Visits: 164,496
kk.86manu (10/25/2012)
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.



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.

I believe in Codd
... and Thinknook is my Chamber of Understanding
Post #1376923
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse