MS SQL Permissions
-
I'm sure he uses DELETE+CREATE simply because that's what MS Sql Server Management Studio auto-generates for you when you tell it to export. Clearly MS SQL Server's auto-generate method is beyond reproach.
@blakey I'm not asking any questions at all. I'm saying your method is stupid and you deserve what you get from it.
-
Yeah, it's pretty obvious that TRWTFs here are Object Explorer and your DBAs. Not much questioning required, really.
-
I'm sure he uses DELETE+CREATE simply because that's what MS Sql Server Management Studio auto-generates for you when you tell it to export.
Meh...
CREATE OR REPLACE VIEW...FTW
-
The funny part is that I agree with @blakeyrat about it being generally retarded that the DBA left him with replace permissions but no view permissions.
But I also think running a blind DELETE or REPLACE statement is even more retarded.
-
Don't be hating on object Explorer, he's my bro. He always reminds me of the fields and indexes I need when there's no consistency between tables, and there are 7 variations of account id
-
So what's your stance on global temp tables? When was the last time you checked before auto dopping ##temp?
Global temp tables are stupid in MSSQL. Oracle's implementation is sane and should be used where appropriate.You do realize that just closing the query window for the connection that create ##temp will drop it, right? Anything that fragile shouldn't be depended on.
-
The funny part is that I agree with @blakeyrat about it being generally retarded that the DBA left him with replace permissions but no view permissions.
Everyone thinks that is retarded.
But I also think running a blind DELETE or REPLACE statement is even more retarded.
Well...it's a development environment, so who cares. This is not much different than making him recompile a library.
-
Don't be hating on object Explorer, he's my bro.
I don't hate it. I don't know anything about it except that it appears to not be able to show @blakeyrat that something exists. Unless it's the way @blakeyrat is using it. I really have no idea.
-
I'm aware they die at connection drop, but when was the last time you checked before auto issuing a destroy command and recreate? My guess is never. It's the same concept. Why is your default mode hurrdurr, nuke and recreate without checking?
-
It's just how blakey is using it. Object Explorer shows all databases, permissions, objects, views procedures, functions, indexes, keys, constraints, etc
Unless somebody explicitly disallows it for your permissions.
-
No, my default mode is "I want to use this source code instead of whatever source it has at the moment". DROPing and CREATEing does that. Most importantly, it does that in a way that you can check the actual script you ran into source control and it can be deployed safely. It is way more important to me to ensure that the object gets deployed properly than it is to preserve the old version of something that I'm in the middle of modifying.
Also, the old versions are in source control. If I dropped every non-data object in the database, I could get them all back.
-
Why would you send the ALTER command to QA instead of FIXING THE ORIGINAL SCRIPT AND SENDING THAT? Seriously.
I think the point is that if you got it wrong in the first instance, you simply fix the bit you got wrong.
The workflow is this:
- Script that creates sproc/view is developed and deployed to server,
- QA finds a bug
- Developer fixes script
- Script is redeployed to the server
If you use DROP/CREATE, you can run the script "as is" any number of times - the previous deployments will be deleted before you create them again.
Otherwise, you would have to ensure that you're ALTERing objects as they are now (that is, as most recenctly created) and that they actually exist, before you re-run the script.
-
Then this situation doesn't impact you, because you don't issue a drop automatically as the first thing you do. You'd get a warning it exists and presumably use a different name.
My point is, if Microsoft locked two or more permissions together, blakey or somebody like blakey would be bitching they couldnt change just the one.
-
Also, the old versions are in source control. If I dropped every non-data object in the database, I could get them all back.
Then what's the complaint? If you accidentally drop something you need, just pull it back from source control. If you don't want to accidentally drop things and have to pull them back from source control, stop issuing blind delete/replace commands.
-
Don't forget about reverting. If I deployed a CREATE script, followed by an ALTER script. Then I would have to modify the first version to an ALTER script to roll back a deployment. If they are all DROP/CREATE, then you roll back by deploying the first script again.
-
A point worth making.
-
Then what's the complaint? If you accidentally drop something you need, just pull it back from source control. If you don't want to accidentally drop things and have to pull them back from source control, stop issuing blind delete/replace commands.
The important point is that I want to test with deployment-ready scripts. Your technique gives you the correct object in the database, but then leaves you with the additional task of creating a deployment script.
-
So when you publish to production, you seriously publish like this:
1)CREATE broken view/table/sp
2) FIX broken view/table/sp
??Even that is still fine, since if you issue the create on production and it already exists, that step will fail and you wouldn't continue to step 2. So there is zero loss here.
The problem is the blind DELETE + CREATE, or blind REPLACE as the FIRST step.
Which he apparently doesn't even do, and then argues with me about it?
-
There's 1 simple question here:
Is either of these your first step to create a table/view/sp:
DROP XXXX; CREATE XXXX;orREPLACE XXXX;I seriously doubt it. Your FIRST step is: CREATE XXXXX; Which in this case from the OP would FAIL DUE TO ALREADY EXISTING. At which point you'd stop and NOT DELETE THE THING THAT IS ALREADY THERE. Right? So where is the problem? Don't issue BLIND DELETE commands to production. Ever. Seriously.
-
Nope. All scripts are deployment safe, so you just deploy the most recent version of it. Our rule is that all object scripts must be idempotent and should not produce errors on success. So, we surround the DROP with a condition to check if it already exists - like everyone else on the planet does.
Tables are different. No DROP is allowed in a table script because that would be stupid.
-
Tables are different. No DROP is allowed in a table script because that would be stupid.
Which is exactly what I'm saying.
So, we surround the DROP with a condition to check if it already exists
Still, on your very first attempt in the dev environment to create the new view/sp, would you run the conditional drop statement? Because you're risking losing an existing SP in the cases where your DBA is an asshat and didn't give you view permissions on an existing object. The very first thing would be to run CREATE. Then when you submit the script to QA/Deploy, then you would add theif(exists) drop xxx;, or if you ended up needing to recreate the view/sp/table after you've already created it on dev once.
-
The fact that CREATE and ALTER are 2 different commands and you'd have to be completely retarded to "accidentally" ALTER a view when trying to CREATE a new one?
He didn't just ALTER. He did a DROP-and-CREATE. Apparently standard practice everywhere he has worked.
-
Keep reading @abarker
-
Keep reading @abarker
Yeah, Dicsourse is a barrier to catching up before replying. I was getting sick of blakey saying "read back 40 posts" instead of typing two sentences.
Seems you and I are pretty much in agreement though. DROPping when you don't expect the object to be there is just dumb.
-
Still, on your very first attempt in the dev environment to create the new view/sp, would you run the conditional drop statement?
Because I don't create objects, I create scripts. I can't tell you how many times I've had a developer tell me "I fixed it!", but then he can't remember what he actually did, so it's hard to deploy the fix.So, all database changes are done in scripts and all are checked into source control. We have a template that has the DROP/CREATE header on it so you don't have to remember to change the script after the first time it's deployed. We run the scripts as a check-in trigger in the source control system. No one should be modifying the database schema directly, you are supposed to check in a script to modify the schema, so we know what you actually ran.
The odds of having the problem you describe are insanely low. If I was concerned about it, I would put in a pre-commit script to reject commits where the object dropped is different from the object being created.
-
To darkmatters point, step 1 in dev is a plain create, this create can't fail. If it does, you pick a different name if there's a name collision. Only then do you add if exists drop and recreate.
-
To darkmatters point, step 1 in dev is a plain create, this create can't fail. If it does, you pick a different name if there's a name collision. Only then do you add if exists drop and recreate.
Why force you developers to modify the script for the first modification rather than just giving them a template that "just works"?
-
Because you RISK DROPPING AN EXISTING SCRIPT SILENTLY
-
You're aware that in your template you can comment out the if drop, or highlight run just the create, and still use the same template?
-
You are aware that then you aren't testing the script that you're going to deploy, right?
-
You are aware that then you aren't testing the script that you're going to deploy, right?
DURKA DURKA JIHAD!
-
I would put in a pre-commit script to reject commits where the object dropped is different from the object being created.
How does this help when you are worried about dropping something with the same name that does a different job than the new object? Your pre-commit scripts must be very intense if they are able to do this level of analysis. Want to help me write a new DB engine?
Because you RISK DROPPING AN EXISTING SCRIPT SILENTLY
Exactly. The main database I work in has hundreds of tables, thousands of functions, and tens-of-thousands of sprocs. Fortunately, due to naming conventions, checking for an existing name in the tables and sprocs is pretty easy. However, with the way SQL Server separates out the functions, it's a PITA to check if a given function name is in use. It's much easier to test to see if the name is in use. And I actually tend to do so with the sprocs as well. Because it's safer.
-
bakalah! mohammad jihad durka durka durka
Or something?
-
You are an amazingly bad sql writer if you think this statement is true.
-
The main database I work in has hundreds of tables, thousands of functions, and tens-of-thousands of sprocs. Fortunately, due to naming conventions, checking for an existing name in the tables and sprocs is pretty easy. However, with the way SQL Server separates out the functions, it's a PITA to check if a given function name is in use. It's much easier to test to see if the name is in use. And I actually tend to do so with the sprocs as well. Because it's safer.
Our scripts are named the same as the object. If you can create and check in the script, it's guaranteed to be a unique object name. You have strange problems.
-
Our scripts are named the same as the object. If you can create and check in the script, it's guaranteed to be a unique object name. You have strange problems.
In that case, the OP problem would not apply to you. So there's not much to argue about anymore.
-
So, all of you people who just open Management Studio and directly modify objects in the development environment - how do you know what changes to deploy? How do you separate your changes from the changes someone else is making for a completely unrelated effort? I've seen people modify a procedure, test it, and then deploy the procedure that exists in the dev database, but happened to have been modified a few minutes ago by a coworker who was fixing a bug. Of course, that bug fix wasn't tested yet, but it got deployed anyways.
-
-
So, all of you people who just open Management Studio and directly modify objects in the development environment - how do you know what changes to deploy? How do you separate your changes from the changes someone else is making for a completely unrelated effort? I've seen people modify a procedure, test it, and then deploy the procedure that exists in the dev database, but happened to have been modified a few minutes ago by a coworker who was fixing a bug. Of course, that bug fix wasn't tested yet, but it got deployed anyways.
Personally? Right now the only people allowed to mess with the database without explicit approval are myself and my boss. Since he only deals with creating new sprocs and functions and modifying table structures, and I do all the debugging (as well as some creation) we haven't run into any issues yet. Plus, we do have a script repository that we use, so it's not like anything is really lost.
-
So.... "management by not letting anyone else do anything". What will you do when you need to scale up the operation?
-
So.... "management by not letting anyone else do anything". What will you do when you need to scale up the operation?
Generally, as operations scale they add new procedures to accommodate the new reality. This is true of any industry: manufacturing, food processing, and programming, to name a few.
-
Why am I getting the impression that this is seen as a lot more complicated than it is?
DROP and CREATE is really quite simple:
-
If the object exists, drop it (otherwise, do nothing). This guarantees a consistent initial state, whether the object already exists or not.
-
Create the object.
Such a script can be run any number of times in any environment. It can be used for rollback (as @Jaime pointed out) or it can be modified in order to provide a new version of the object.
This is completely aside from @blakeyrat's original issue.
However, on reflection, there's actually no good reason why he should need to see the object in question (if it is a sproc/view) - or even know it exists - when using such a procedure - provided he's not doing the naming.
The main danger stems from the fact that a script of this sort will work whether an object exists or not. The objects themselves, however, are cheap - as in: quick and easy to replace with "last known good" scripts. If a developer accidentally overwrites a different object than he was supposed to, because he got the name wrong - it is a question of developer negligence, but easily rectified.
On the permissions side of things, if we look at it from a minimum privilege perspective, all a developer needs to write such scripts are CREATE and DROP permissions. As long as the names are provided by someone who knows the entire schema, there's no need for a developer to see the objects he is creating.
(Note that all of the above only applies to easily replacable objects like sprocs and views. If you try it with tables, any damage will be purely self-inflicted.)
-
-
This is completely aside from @blakeyrat's original issue.
Not entirely, because @blakeyrat admits that this is SOP where he works. As a result, he wouldn't have encountered this issue if he had done something like commenting out the DROP on first run. If he had started with a simple CREATE on the first run, he would have received a error, and this topic would have taken an somewhat different track.
-
Yes, which is why I addressed that in the latter part of my post.
-
So, all of you people who just open Management Studio and directly modify objects in the development environment - how do you know what changes to deploy?
Who are you talking to? I run ALTER / CREATE scripts the exact same way you do. I just take the time to look and figure out which one it should be rather than just dropping whatever was there before.
Also, who has multiple people editing the same deployment script at the same time? Do you have no source control at all?
-
Also, someone REALLY likes my MS SQL Server opinions or just wanted to spam the hell out of me....
Also, also. I use also too much.
-
I tend to like posts I agree with on topics I don't care enough to contribute to. I guess I basically agree with everything you said?
-
I guess I basically agree with everything you said?
It's getting a little stalkerish.
-
I've given out something like 68 likes today alone, I'm hardly keeping track of how many of them go to any individual person.
-
I've given out something like 68 likes today alone, I'm hardly keeping track of how many of them go to any individual person
Well now I don't feel special anymore.
