May 25, 2012 at 10:22 am
Revenant (5/25/2012)
Lynn Pettis (5/25/2012)
Could I get a few more people to look at the last few posts in this thread please? I want to be sure what I said is possibly the answer to the problem.Thanks
Yes, it is, without that "possibly."
Okay, TGIF, it took me a bit to figure out what you were saying, but I finally did. Thank you
May 25, 2012 at 10:26 am
Lynn Pettis (5/25/2012)
Revenant (5/25/2012)
Lynn Pettis (5/25/2012)
Could I get a few more people to look at the last few posts in this thread please? I want to be sure what I said is possibly the answer to the problem.Thanks
Yes, it is, without that "possibly."
Okay, TGIF, it took me a bit to figure out what you were saying, but I finally did. Thank you
Also, to be a bit more specific, if any of you concur (and I have one concurrance) would you please leave a short post over there as well? It may help assure the OP if more than one person has come to the same conclusion. Just don't dog pile.
May 25, 2012 at 10:39 am
SQL Kiwi (5/24/2012)
RBarryYoung (5/24/2012)
Is it my imagination or is Paul getting younger?😀
I wish :Wow:
Heh. Me too, I was going to ask for your secret. 🙁
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 25, 2012 at 10:49 am
Anyone else find it inconvenient that MS wants people to have SysAdmin access in order to work the sp_enable_sql_debug proc correctly?
I've just started running into this issue myself. I hope you'll vote to put this on the list of things to be fixed.
May 25, 2012 at 12:15 pm
RBarryYoung (5/24/2012)
GilaMonster (5/23/2012)
dwain.c (5/22/2012)
Stefan Krzywicki (5/22/2012)
Lynn Pettis (5/22/2012)
If people would just format their code they could figure out some of their own problems. Geez.Many should format it all at once, like with
Format C:\Perhaps the next time someone asks a really silly SQL question, someone will suggest this to solve their problem:
EXEC xp_cmdshell 'format c:\'
No, because chances are someone (the OP or a later visitor) will actually try it.
Heh. You don't know how many times I've almost posted a script to automatically find and drop every stored procedure that has a cursor in it. This is The only thing tthat stops me ... :w00t:
I've been tempted, but the necessity of including a complete T-SQL parser in the script so that I can distinguish scripts with a cursor in them from scripts with the word "cursor" in a comment ("-- this is how a cursor is avoided...." or something like that) or a table, view, cte, column, or function name which includes the string "cursor" or a string constant containg the word "cursor" has made it just too much effort for me to write. It would be OK if the BNF in BoL were correct, but I've noticed a few errors there over the years and I don't want to have to validate/correct all of it. :hehe:
Tom
May 25, 2012 at 5:31 pm
Brandie Tarvin (5/25/2012)
Anyone else find it inconvenient that MS wants people to have SysAdmin access in order to work the sp_enable_sql_debug proc correctly?
I've not run into that problem because I generally don't allow folks to debug in production and I give developers SA privs on the development box(es). For the most part, Developers are lucky if they even know the name of the production server in a shop that I've locked down. Of course, I also take very good care of the Developers and make sure that they always have a recent copy of the production databases to work from.
In the event of a "crisis" (which doesn't happen very often anymore because of the process of code review, Unit Testing, Integration Testing, and QA that we have setup), the "Pro Access" system will be instantiated to give the Lead Developer or the particular SME for the troubled area varying levels of access to quickly troubleshoot the production problem. Then, access to prod for the involved Developers is removed. The "problem" is quickly transferred to the Dev box where the fix is created, tested, reviewed, etc, and we promote the "fix" code just like any other code during a change control. We've gotten very good at it and it doesn't take much time at all.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 25, 2012 at 5:54 pm
Jeff Moden (5/25/2012)
Brandie Tarvin (5/25/2012)
Anyone else find it inconvenient that MS wants people to have SysAdmin access in order to work the sp_enable_sql_debug proc correctly?I've not run into that problem because I generally don't allow folks to debug in production and I give developers SA privs on the development box(es). For the most part, Developers are lucky if they even know the name of the production server in a shop that I've locked down. Of course, I also take very good care of the Developers and make sure that they always have a recent copy of the production databases to work from.
In the event of a "crisis" (which doesn't happen very often anymore because of the process of code review, Unit Testing, Integration Testing, and QA that we have setup), the "Pro Access" system will be instantiated to give the Lead Developer or the particular SME for the troubled area varying levels of access to quickly troubleshoot the production problem. Then, access to prod for the involved Developers is removed. The "problem" is quickly transferred to the Dev box where the fix is created, tested, reviewed, etc, and we promote the "fix" code just like any other code during a change control. We've gotten very good at it and it doesn't take much time at all.
+1 to what Jeff wrote. I'm not sure I'd give developers sysadmin on the dev server. They can run debugs on their local instance, where they can pick up the pieces if need be.
May 26, 2012 at 7:46 pm
Steve Jones - SSC Editor (5/25/2012)
Jeff Moden (5/25/2012)
Brandie Tarvin (5/25/2012)
Anyone else find it inconvenient that MS wants people to have SysAdmin access in order to work the sp_enable_sql_debug proc correctly?I've not run into that problem because I generally don't allow folks to debug in production and I give developers SA privs on the development box(es). For the most part, Developers are lucky if they even know the name of the production server in a shop that I've locked down. Of course, I also take very good care of the Developers and make sure that they always have a recent copy of the production databases to work from.
In the event of a "crisis" (which doesn't happen very often anymore because of the process of code review, Unit Testing, Integration Testing, and QA that we have setup), the "Pro Access" system will be instantiated to give the Lead Developer or the particular SME for the troubled area varying levels of access to quickly troubleshoot the production problem. Then, access to prod for the involved Developers is removed. The "problem" is quickly transferred to the Dev box where the fix is created, tested, reviewed, etc, and we promote the "fix" code just like any other code during a change control. We've gotten very good at it and it doesn't take much time at all.
+1 to what Jeff wrote. I'm not sure I'd give developers sysadmin on the dev server. They can run debugs on their local instance, where they can pick up the pieces if need be.
Giving developers admin rights is counterproductive because their code is developed and tested as admin, and in many cases fails when run as a user or service.
May 26, 2012 at 9:01 pm
Revenant (5/26/2012)
Steve Jones - SSC Editor (5/25/2012)
Jeff Moden (5/25/2012)
Brandie Tarvin (5/25/2012)
Anyone else find it inconvenient that MS wants people to have SysAdmin access in order to work the sp_enable_sql_debug proc correctly?I've not run into that problem because I generally don't allow folks to debug in production and I give developers SA privs on the development box(es). For the most part, Developers are lucky if they even know the name of the production server in a shop that I've locked down. Of course, I also take very good care of the Developers and make sure that they always have a recent copy of the production databases to work from.
In the event of a "crisis" (which doesn't happen very often anymore because of the process of code review, Unit Testing, Integration Testing, and QA that we have setup), the "Pro Access" system will be instantiated to give the Lead Developer or the particular SME for the troubled area varying levels of access to quickly troubleshoot the production problem. Then, access to prod for the involved Developers is removed. The "problem" is quickly transferred to the Dev box where the fix is created, tested, reviewed, etc, and we promote the "fix" code just like any other code during a change control. We've gotten very good at it and it doesn't take much time at all.
+1 to what Jeff wrote. I'm not sure I'd give developers sysadmin on the dev server. They can run debugs on their local instance, where they can pick up the pieces if need be.
Giving developers admin rights is counterproductive because their code is developed and tested as admin, and in many cases fails when run as a user or service.
I always have TestUser accounts for that.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 26, 2012 at 9:08 pm
L' Eomot Inversé (5/25/2012)
RBarryYoung (5/24/2012)
GilaMonster (5/23/2012)
dwain.c (5/22/2012)
Stefan Krzywicki (5/22/2012)
Lynn Pettis (5/22/2012)
If people would just format their code they could figure out some of their own problems. Geez.Many should format it all at once, like with
Format C:\Perhaps the next time someone asks a really silly SQL question, someone will suggest this to solve their problem:
EXEC xp_cmdshell 'format c:\'
No, because chances are someone (the OP or a later visitor) will actually try it.
Heh. You don't know how many times I've almost posted a script to automatically find and drop every stored procedure that has a cursor in it. This is The only thing tthat stops me ... :w00t:
I've been tempted, but the necessity of including a complete T-SQL parser in the script so that I can distinguish scripts with a cursor in them from scripts with the word "cursor" in a comment ("-- this is how a cursor is avoided...." or something like that) or a table, view, cte, column, or function name which includes the string "cursor" or a string constant containg the word "cursor" has made it just too much effort for me to write. It would be OK if the BNF in BoL were correct, but I've noticed a few errors there over the years and I don't want to have to validate/correct all of it. :hehe:
Heh, try searching SQL_MODULES for '%cursor%open%while%fetch%close%deallocate%', Tom. Those suckers have cursors in them!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 27, 2012 at 4:50 am
RBarryYoung (5/26/2012)
Heh, try searching SQL_MODULES for '%cursor%open%while%fetch%close%deallocate%', Tom. Those suckers have cursors in them!
Well, it's possible that that could occur within a string literal (or split across several string literals, or a mixture of comments and string literal) but that's so unlikely that I'd probably go with it except for one little problem: the sequence occurring within a single does happen. It happens when an SP has been maintained for a while by someone who follows the lunatic convention that old code is commented out rather than deleted, and eventually deleted after new code has been in service X amount of time. Unfortunately people who write SQL develop crazy conventions like that if they've never come across any sort of real source control, versioning, and archive system (and some commercially available source control systems are perhaps sufficiently awful to drive people to do something crazy like that). It used to happen in other languages too, (where one of its side effects is eliminated: the source text doesn't occupy memory at run time). So the best you can say is that something which matches that either has or used to have a cursor in it. So you have to be able to parse well enough to separate comments from code (which involves separating comment delimiters from the same character sequences occurring withing string literals or within comments).
Tom
May 27, 2012 at 10:44 am
L' Eomot Inversé (5/27/2012)
RBarryYoung (5/26/2012)
Heh, try searching SQL_MODULES for '%cursor%open%while%fetch%close%deallocate%', Tom. Those suckers have cursors in them!Well, it's possible that that could occur within a string literal (or split across several string literals, or a mixture of comments and string literal) but that's so unlikely that I'd probably go with it except for one little problem: the sequence occurring within a single does happen. It happens when an SP has been maintained for a while by someone who follows the lunatic convention that old code is commented out rather than deleted, and eventually deleted after new code has been in service X amount of time. Unfortunately people who write SQL develop crazy conventions like that if they've never come across any sort of real source control, versioning, and archive system (and some commercially available source control systems are perhaps sufficiently awful to drive people to do something crazy like that). It used to happen in other languages too, (where one of its side effects is eliminated: the source text doesn't occupy memory at run time). So the best you can say is that something which matches that either has or used to have a cursor in it. So you have to be able to parse well enough to separate comments from code (which involves separating comment delimiters from the same character sequences occurring withing string literals or within comments).
Yep, you're right. I guess my destructive fantasies just aren't as conscientious as yours, Tom. 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 27, 2012 at 11:20 am
Revenant (5/26/2012)
Steve Jones - SSC Editor (5/25/2012)
Jeff Moden (5/25/2012)
Brandie Tarvin (5/25/2012)
Anyone else find it inconvenient that MS wants people to have SysAdmin access in order to work the sp_enable_sql_debug proc correctly?I've not run into that problem because I generally don't allow folks to debug in production and I give developers SA privs on the development box(es). For the most part, Developers are lucky if they even know the name of the production server in a shop that I've locked down. Of course, I also take very good care of the Developers and make sure that they always have a recent copy of the production databases to work from.
In the event of a "crisis" (which doesn't happen very often anymore because of the process of code review, Unit Testing, Integration Testing, and QA that we have setup), the "Pro Access" system will be instantiated to give the Lead Developer or the particular SME for the troubled area varying levels of access to quickly troubleshoot the production problem. Then, access to prod for the involved Developers is removed. The "problem" is quickly transferred to the Dev box where the fix is created, tested, reviewed, etc, and we promote the "fix" code just like any other code during a change control. We've gotten very good at it and it doesn't take much time at all.
+1 to what Jeff wrote. I'm not sure I'd give developers sysadmin on the dev server. They can run debugs on their local instance, where they can pick up the pieces if need be.
Giving developers admin rights is counterproductive because their code is developed and tested as admin, and in many cases fails when run as a user or service.
Although I agree that's certainly a possibility, I've found that it's an extremely rare problem (haven't actually seen it as a problem, to be honest). Since it is so rare, I'd rather have the Developers be able to operate quite freely on the Dev box because, in my experience, it's more of a pain to limit the Developers than it is to deal with the rare instance of privs problems when the code hits QA. Even at that, we're a 100% code review shop and I'd likely catch such problems well before they even come close to QA. If the problems do make it to QA, they're usually quite easy to deal with.
As a sidebar, I have some pretty good Developers I'm working with. Letting them have free reighn in Dev shows that I have a certain amount of trust in them. It goes a long way in the relationship between me as the DBA and the Developers. They've also been trained by yours truly and understand that they need to keep such problems from occurring. They also know that I applaud innovation and that if they write some innovative code that requires some form of special priv, that I'll help them with the work around or make it so the code has the priv without elevating user privs.
Overall, I've found that giving trained, educated, and thoughtful Developers the ability to try anything they want has led to some real improvements in code... and attitude.
Admittedly, this isn't going to work in many shops because some DBAs don't spend the time to train the Developers they work with, they can't be bothered with or don't have the time for 100% code reviews. I use the code reviews to provide additional training for the Developers ("Here... that's really cool what you did but let me show you a better way to accomplish the same thing.)
The hidden benefits to all of this have been a drop in rework to near zero and retention off Developers. The Devs are a lot happier because they feel trusted, enabled, and accomplished. They also feel that they can come to me with any problem whereas they used to shy away from asking for help.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 29, 2012 at 4:27 am
Jeff Moden (5/27/2012)
Revenant (5/26/2012)
Steve Jones - SSC Editor (5/25/2012)
Jeff Moden (5/25/2012)
Brandie Tarvin (5/25/2012)
Anyone else find it inconvenient that MS wants people to have SysAdmin access in order to work the sp_enable_sql_debug proc correctly?I've not run into that problem because I generally don't allow folks to debug in production and I give developers SA privs on the development box(es). For the most part, Developers are lucky if they even know the name of the production server in a shop that I've locked down. Of course, I also take very good care of the Developers and make sure that they always have a recent copy of the production databases to work from.
In the event of a "crisis" (which doesn't happen very often anymore because of the process of code review, Unit Testing, Integration Testing, and QA that we have setup), the "Pro Access" system will be instantiated to give the Lead Developer or the particular SME for the troubled area varying levels of access to quickly troubleshoot the production problem. Then, access to prod for the involved Developers is removed. The "problem" is quickly transferred to the Dev box where the fix is created, tested, reviewed, etc, and we promote the "fix" code just like any other code during a change control. We've gotten very good at it and it doesn't take much time at all.
+1 to what Jeff wrote. I'm not sure I'd give developers sysadmin on the dev server. They can run debugs on their local instance, where they can pick up the pieces if need be.
Giving developers admin rights is counterproductive because their code is developed and tested as admin, and in many cases fails when run as a user or service.
Although I agree that's certainly a possibility, I've found that it's an extremely rare problem (haven't actually seen it as a problem, to be honest). Since it is so rare, I'd rather have the Developers be able to operate quite freely on the Dev box because, in my experience, it's more of a pain to limit the Developers than it is to deal with the rare instance of privs problems when the code hits QA. Even at that, we're a 100% code review shop and I'd likely catch such problems well before they even come close to QA. If the problems do make it to QA, they're usually quite easy to deal with.
As a sidebar, I have some pretty good Developers I'm working with. Letting them have free reighn in Dev shows that I have a certain amount of trust in them. It goes a long way in the relationship between me as the DBA and the Developers. They've also been trained by yours truly and understand that they need to keep such problems from occurring. They also know that I applaud innovation and that if they write some innovative code that requires some form of special priv, that I'll help them with the work around or make it so the code has the priv without elevating user privs.
Overall, I've found that giving trained, educated, and thoughtful Developers the ability to try anything they want has led to some real improvements in code... and attitude.
Admittedly, this isn't going to work in many shops because some DBAs don't spend the time to train the Developers they work with, they can't be bothered with or don't have the time for 100% code reviews. I use the code reviews to provide additional training for the Developers ("Here... that's really cool what you did but let me show you a better way to accomplish the same thing.)
The hidden benefits to all of this have been a drop in rework to near zero and retention off Developers. The Devs are a lot happier because they feel trusted, enabled, and accomplished. They also feel that they can come to me with any problem whereas they used to shy away from asking for help.
Piling on with Jeff, I'd rather enable the developers to keep them going as fast as possible in the dev environment. I've always had at least one layer between dev & production, sometimes two or three. That's where I could catch any silliness with security before it got out to the wild. A couple of missed QA deployments or a failed CI build with all the noise those generates and the developers are back on the straight & narrow again.
"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
May 29, 2012 at 4:42 am
We don't give Devs more than db_owner in the databases they need to have. For one, we do have a DBA admin only database that we develop code in for production which has data that is restricted to our team. For another, we have developers who write code and forget to add WHERE clauses.
The way our environment is set up is to allow least necessary permissions so we know before we move up to test or QA what permissions will be required for our applications. That way, we're not wasting time during testing trying to figure out why permissions won't work.
We've never had an instance where we've needed the Devs to have SysAdmin access. DB_Owner, yes, but not SysAdmin. That's just asking for trouble given some of the things our Devs like to "experiment" with.
EDIT: I should add, it wasn't one of the Devs asking for debug access anyway. It was one of our analysts. When I asked, he told me he was trying to step through code in SSMS so he could see the value of a variable at certain points. At which point I told him to just do a SELECT on the variables as he ran through the code manually. Don't need debug access for that.
Still, it would be nice to give that kind of access without granting someone SysAdmin perms.
Viewing 15 posts - 36,286 through 36,300 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply