SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Login restriction


Login restriction

Author
Message
kk.86manu
kk.86manu
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 303
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)

Group: General Forum Members
Points: 227837 Visits: 46339
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

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


Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13472 Visits: 4077
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;-)
kk.86manu
kk.86manu
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 303
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
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13472 Visits: 4077
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;-)
kk.86manu
kk.86manu
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 303
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.
Thinknook
Thinknook
Mr or Mrs. 500
Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)

Group: General Forum Members
Points: 595 Visits: 164990
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)

Group: General Forum Members
Points: 227837 Visits: 46339
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

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


Thinknook
Thinknook
Mr or Mrs. 500
Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)

Group: General Forum Members
Points: 595 Visits: 164990
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search