User permissions

Question

User permissions

Casper101

Hall of Fame

Points: 3903
More actions
March 19, 2009 at 5:41 am

#129113

I want to create a user and give this user Stored proc execute permissions only.
I don't want to give him read or write permission as yet.
My question is: If he has no specific read or write permissions, will he still be able to execute any SP - even if the SP itself is doing a SELECT or UPDATE or INSERT, etc ?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

JamieX Hall of Fame Points: 3763 More actions · Answer 1

Yes, search google for something along the lines of "SQL Server Stored Procedure Security" and you will find lots of articles on best practices with SP's and security.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

The answer has to be maybe. If you don’t violate the rules for ownership chain, then the answer would be yes, but if you did something that breaks the ownership chain, then the answer would be no. Things that can break the ownership chain are – using dynamic SQL, referring to objects that are stored in a different database, referring to an object that the creator of the procedure has no permissions to use, etc’.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Casper101 Hall of Fame Points: 3903 More actions · Answer 3

Thanks for the replies.

One more permission related issue: If I execute this: EXEC sp_addrolemember 'db_datareader', 'User_Name_Here'

It give the user SELECT permissions on tables , but not views.

How can I give the user permissions to SELECT from VIEWS?

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

Casper (3/20/2009)
Thanks for the replies.
One more permission related issue: If I execute this: EXEC sp_addrolemember 'db_datareader', 'User_Name_Here'
It give the user SELECT permissions on tables , but not views.
How can I give the user permissions to SELECT from VIEWS?

Actually it does give select permissions on views. If you encountered a problem then maybe your view is trying to select data from a table that belongs to a different database, or is using a user defined function. Can you post more details about the view and also the error that you are getting? In any case the small script bellow shows that a user that was added to db_datareader role, can use select statement that is based on a view.

use tempdb

go

--Creating the login and user

--and add the user to db_datareader role

create login MyTestLogin with password = '12#$aBcd'

go

create user MyTestLogin for login MyTestLogin

go

exec sp_addrolemember 'db_datareader', MyTestLogin

go

--Creating the table and the view

create table MyTable (i int)

insert into MyTable (i) values (1)

go

create view MyView

as

--user_name() will show the user that is executing the query

select user_name() as UserName, i from MyTable

go

execute as user = 'MyTestLogin'

select * from MyTable

--notice that the test user can use the query

select * from MyView

go

revert

--cleanup

drop user MyTestLogin

drop login MyTestLogin

drop table MyTable

drop view MyView

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Casper101 Hall of Fame Points: 3903 More actions · Answer 5

Thanks - you were right - the view was referencing another DB - So I: GRANT SELECT ON tableName TO userName (for that DB)

and it works fine

Thanks