April 9, 2016 at 1:09 am
Hello team, i encountered a strange (at least to me) issue when performing Alter authorization.
Here is the scenario:
I have 2 SPs -SP_1 & SP_2 (schema - dbo ; owner - dbo) (SP_1 inserts data into Table_1 , Sp_2 inserts into T_2)
User_1 (has execute permission on those 2 SPs)
I created a new role - Role_1 - DENY'd INSERT to Table_1.
I perform
ALTER AUTHORIZATION OBJECT::SP_1 TO Role_1
&
sp_addrolemember User_1 , Role_1
so when i execute SP_1 under , i get INSERT permission denied on Table_1 - Perfect for my requirement.
Now i do: ALTER AUTHORIZATION OBJECT::SP_1 TO SCHEMA OWNER
I expected that User_1 will retain his EXEC permission on SP_1 , but looks like ALTER AUTH statment removed it form User_1.
1. Is this expected behaviour?
2. If yes, how do i work around it ? (ALTER AUTH to/from a DENY role, i should preserve users previous permissions)
3. If no, can you please explain what i could have done incorrectly?
April 9, 2016 at 2:53 am
If you read the topic for ALTER AUTHORIZATION in Books Online, you will find this sentence: If the target entity is not a database and the entity is being transferred to a new owner, all permissions on the target will be dropped. (I will have to admit that I was not aware of this myself.)
In the first case when you transferred ownership to the role you did not get any permission error, since the user was a member of the owning role.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
April 9, 2016 at 10:21 am
Thanks, Erland. That makes sense. But are you aware of any way to work around it?
My requirement:
We have 2 database servers that are replicated via third party replication technology. I want all my apps to connect to Sever 1 and only few apps on Server 2. On Server 2, I should restrict all INSERTs except for those few apps. i thought of ALTER AUTH of SPs to a role that has DENY INSERTs. But what happened was, all explicit permissions that i gave on SP1 were dropped. I want to retain them alter auth, so i can switch back and forth between servers. Is that possible?
April 9, 2016 at 11:38 am
Too many unknowns to say anything with certainty. Maybe you should go back to the drawing board and find a different solution.
One idea, but I don't know if it fits, is to have everything owned by roles, and then you take users in and out of the roles as need, and you don't really need to grant that much at all.
But I would have to understand the situation better to give better advice.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply