| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2014-05-21 : 16:33:33
|
| This is more of a Political question, btw.We have a dev environment and the Developers kept requesting we go from SQL Server Authentication to Windows Authentication. So I did this.I mentioned ahead of time to the Developers: don't forget to add 'dbo.' when you create your Stored Procedures. Otherwise, the SPs will be owned by the various Developers rather than dbo.We have some Developers who keep forgetting this, in spite of my several reminders.What should I do? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2014-05-21 : 16:55:54
|
in the dev environment can all the developers be members of the db_owner role?sounds like you have dev and prod - what about QC? Don't these errors get caught there? (as long as we're being political  )EDIT:Assuming the errors are caught prior to deployment to the production environment the release should be sent back to the dev team. Otherwise why should they change when it always goes into production right?Be One with the OptimizerTG |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-05-21 : 17:57:22
|
| I agree with TG. The DBA should not have to worry about this as it'll first break in QA and a bug will be submitted for the dev team to fix. After a few of these, the devs will remember to use dbo.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2014-05-22 : 09:29:08
|
Thanks.quote:
Originally posted by TG
in the dev environment can all the developers be members of the db_owner role?
They are. But with a Create, it can not, as far as I know, be defaulted to dbo. When 'dbo.' is not specified, the SP is owned by the Developer.quote:
sounds like you have dev and prod - what about QC? Don't these errors get caught there? (as long as we're being political )
Yes, we have QC. I'm responsible for moving SPs from Dev to QC. So I catch it right away using my Red Gate tool. But by then it's a little late because QC is expecting to test it right away. Also, my idea is that Developers shouldn't be doing this in the first place. People make mistakes is one thing but it seems to be happening too often!quote:
EDIT:Assuming the errors are caught prior to deployment to the production environment the release should be sent back to the dev team. Otherwise why should they change when it always goes into production right?
Yes, I don't believe any of these have gone to Production, yet. But just frustering that I keep needing to sort this out too often. I guess I haven't been sending the Release back to Dev, it just seems a little much over something like this, i.e. to hold everything up. We're doing scrum so it's all rush, rush, rush. But maybe I should consider saying, if you want this in QA, wait while the Dev fixes this reoccuring problem - yes that may work. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2014-05-22 : 09:35:50
|
quote:
Originally posted by tkizer
I agree with TG. The DBA should not have to worry about this as it'll first break in QA and a bug will be submitted for the dev team to fix. After a few of these, the devs will remember to use dbo.Tara Kizer
Good point. Maybe I should submit a defect myself.I don't think it would get caught by the QA team though. I think if I move it under the ownership of a developer, SQL Server would still execute the SP. Of course if an SP were owned by different developers under different versions or one Developer owns one version, another version is owned by dbo, QA would probably catch that if the wrong version is called. (I'm curious now what version of an SP SQL Server would call if you don't specify the owner and the same sp name exists under different owners) |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2014-05-22 : 10:16:32
|
| >>I'm curious now what version of an SP SQL Server would call if you don't specify the owner and the same sp name exists under different ownersThe DEFAULT_SCHEMA for a user will be the first SP schema version searched for when sql server resolves objects.EDIT:setting that value to dbo - assuming they are in db_owner group - should also resolve the original problem you posted.Be One with the OptimizerTG |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2014-05-22 : 10:23:04
|
quote:
Originally posted by TGThe DEFAULT_SCHEMA for a user will be the first SP schema version searched for when sql server resolves objects.
Thanks. I meant for example, 2 verions under different owners, neither are your Default Schema, but you have rights to both, and you don't specify. Just wondering... |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2014-05-22 : 10:31:55
|
| You mean two different owners neither of which are either dbo or the executors schema? I don't know about that. You could test but I don't even know if sql's choice would be consistent over time or across servers. if it is consistent then I guess the precedence must be one of: username, userid, or sid.Be One with the OptimizerTG |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2014-05-22 : 11:09:18
|
| Thanks I'll try it then... |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2014-05-27 : 13:31:18
|
| To be able to use the default schema - you cannot use an AD group for access. You have to use individual accounts and set the default schema to dbo for each user.Another option would be to implement a policy that prevents creating objects in any schema other than those defined by you. The policy can be implemented to rollback the create and notify the developer.This is just the beginning though - and doesn't resolve the issues where the developers are not schema qualifying objects in their code. Just because they are creating the procedures in the 'right' schema does not mean the rest of the code is referencing the right schema.Overall, if this is an issue - then you need a code review process implemented to review all code prior to that code being moved out of the development environment and into your QA/UAT/QC environment. Any code that gets to that environment with these kinds of issues would then count negatively against the developers on their next review - including the reviewer(s) who signed off on the code review.Start impacting their reviews and you may just see a very quick improvement in their practices. |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-06-24 : 17:44:41
|
| Best is to create a DDL trigger to reject any non-DBA-requested object that is not owned by 'dbo'. If a non-dbo object needs created, the DBA should do it.I also use a DDL trigger to reject all non-sanctioned creates in system dbs (outside tempdb, of course), particularly master. Since master is the default default (sic) db, it's easy to have people accidentally create objects in it instead of a user db. |
|
|
|
|
Developer keeps forgetting to add 'dbo'