October 31, 2018 at 6:50 am
I've got a group of developers who insist of dropping a view or sp when deploying changes. For some reason they don't want to use ALTER and opt for dropping and recreating. Of course I've got to go back and grant the permissions to said view/sp. Has anyone come across a way to capture the permissions prior to dropping and then re-granting them after the object is created.
Thanks
October 31, 2018 at 7:00 am
jon.wilson - Wednesday, October 31, 2018 6:50 AMI've got a group of developers who insist of dropping a view or sp when deploying changes. For some reason they don't want to use ALTER and opt for dropping and recreating. Of course I've got to go back and grant the permissions to said view/sp. Has anyone come across a way to capture the permissions prior to dropping and then re-granting them after the object is created.Thanks
Consider creating a post-deployment script to fix up all permissions after deployments. It should become part of your standard deployment process.
This should all be automated, of course – developers should not be deploying changes themselves.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 31, 2018 at 7:08 am
Developers don't deploy, the scripts are handed off to me and I usually go back and run grant statements. Sometimes things slip through the cracks and I miss something. Of course I could change the drop and create to alter, but I'm not in the habit of modifying their scripts - CYA.
October 31, 2018 at 8:01 am
What is their reasoning for using DROP and CREATE? Considering that they aren't in charge of deployment, they must have a good reason for overuling someone that is.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 31, 2018 at 8:12 am
Good point. I have no idea why they choose not to use ALTER. My guess is that they want to start "fresh" or they don't fully understand ALTER
October 31, 2018 at 8:20 am
jon.wilson - Wednesday, October 31, 2018 8:12 AMGood point. I have no idea why they choose not to use ALTER. My guess is that they want to start "fresh" or they don't fully understand ALTER
Perhaps they need to be educated then. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 31, 2018 at 8:25 am
jon.wilson - Wednesday, October 31, 2018 8:12 AMGood point. I have no idea why they choose not to use ALTER. My guess is that they want to start "fresh" or they don't fully understand ALTER
If you were on 2016+, you could introduce them to CREATE OR ALTER – potentially keeping everyone happy.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 1, 2018 at 1:46 am
It can be done with sys.database_permissions, athough I'd be inclined to fail a code review for any developer that drops and creates an object without having a script to correctly set the permissions (or indeed does anything without including appropriate permission scripts). A few rejected reviews or failed test deployments because they aren't doing things properly might get them to engage their brain a little.
November 1, 2018 at 6:57 am
andycadley - Thursday, November 1, 2018 1:46 AMIt can be done with sys.database_permissions, athough I'd be inclined to fail a code review for any developer that drops and creates an object without having a script to correctly set the permissions (or indeed does anything without including appropriate permission scripts). A few rejected reviews or failed test deployments because they aren't doing things properly might get them to engage their brain a little.
Yeah seriously, if the devs insist on dropping/recreating then make *them* include the appropriate grants in their scripts, That all should be in their source code control system anyway (and if not, well, that sounds like a WTF to me).
November 2, 2018 at 8:45 am
You could add the view, stored procedures, etc. to a schema and grant the appropriate rights on that schema. As long as they always create/amend objects in that schema (easy to enforce), you don't have to worry whether they DROP/CREATE or ALTER after that.
November 2, 2018 at 10:23 am
jon.wilson - Wednesday, October 31, 2018 6:50 AMI've got a group of developers who insist of dropping a view or sp when deploying changes. For some reason they don't want to use ALTER and opt for dropping and recreating. Of course I've got to go back and grant the permissions to said view/sp. Has anyone come across a way to capture the permissions prior to dropping and then re-granting them after the object is created.Thanks
This query will tell you what permissions are granted to sps.SELECT s.name ProcedureSchema,
P.Name ProcedureName,
DP.[type] PermissionType,
U.name UserName
FROM sys.database_permissions DP
INNER JOIN sys.procedures P
ON P.OBJECT_ID = DP.major_Id
INNER JOIN sys.schemas S
ON s.schema_id = p.schema_id
INNER JOIN sys.sysusers U
ON U.[uid] = DP.grantee_principal_id
ORDER BY 1,2
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply