January 4, 2013 at 3:57 am
Hello I've gotten 3 questions where I can't find an answer for, 1 should be simple but my managment studio refuses to cooperate
1)A user has select rights to tables and views
A stored procedure performs selects on the tables
Does the user need the select rights to the tables to get the result from the stored procedure
2)A table is partioned in partitions per continent, can you limit a user access to a single partition
3)The same table but without the partitions and same question can you limit what records a user has rights to
January 4, 2013 at 4:13 am
1 - No, just execute permissions on the proc will be enough. They dont need access to select from the table or views, as long as the ownership chain is intact.
2 - Yes as long as you know the partition function and the partition id and the partioning column
SELECT * FROM Production.TransactionHistory
WHERE $PARTITION.TransactionRangePF1(TransactionDate) = 5 ;
http://msdn.microsoft.com/en-us/library/ms188071.aspx
3 - No
You could create a secondary table with the username and the list of continents they can see, then use a view which joins the two together and passes in the users userid as the filter so they can only see what they are allowed to see. Using something like the SUSER_SNAME() builtin function.
January 4, 2013 at 4:18 am
Resender (1/4/2013)
Hello I've gotten 3 questions where I can't find an answer for, 1 should be simple but my managment studio refuses to cooperate1)A user has select rights to tables and views
A stored procedure performs selects on the tables
Does the user need the select rights to the tables to get the result from the stored procedure
As long as the owner of the stored procedure has rights to the tables, then the users only need execute privileges on the procedure to get access to that data.
2)A table is partioned in partitions per continent, can you limit a user access to a single partition
3)The same table but without the partitions and same question can you limit what records a user has rights to
The answer to both these would be to use a view or a procedure that limits access to the data you want.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 4, 2013 at 4:19 am
anthony.green (1/4/2013)
1 - No, just execute permissions on the proc will be enough. They dont need access to select from the table or views2 - Yes as long as you know the partition function and the partition id and the partioning column
SELECT * FROM Production.TransactionHistory
WHERE $PARTITION.TransactionRangePF1(TransactionDate) = 5 ;
http://msdn.microsoft.com/en-us/library/ms188071.aspx
3 - No
You could create a secondary table with the username and the list of continents they can see, then use a view which joins the two together and passes in the users userid as the filter so they can only see what they are allowed to see. Using something like the SUSER_SNAME() builtin function.
I clearly need to type faster.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 7, 2013 at 1:01 am
Thank you both for the quick answers
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply