March 19, 2009 at 5:41 am
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 ?
March 19, 2009 at 5:58 am
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.
March 19, 2009 at 6:05 am
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/
March 20, 2009 at 1:29 am
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?
March 20, 2009 at 3:09 am
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/
March 20, 2009 at 3:57 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply