Openning a can of Worms ! RRS feed

  • General discussion

  • Hi All,

    I hope I am not going to create a long list of argument here but need a few feedback.

    I have been a SQL Server DBA for over 18 years (Besides a Little bit of Oracle and respectable amount of DB2 DBA but always SQL Server as the primary focus) and I have seen many things course of my past years and I am still doing the job as I believe it is right. For short, I do have a problem with something that pushes me from one company to another (Already changed 3 companies) and that is the people who have SysAdmin permissions on the SQL Servers OTHER than the DBAs. This is a concept I have been struggling with and having arguments with the upper management and at the end quitting that place (If I can not change it to the level kicking all the Non-DBAs out of the SysAdmin group).

    I have a problem grasping why especially developers need SysAdmin permissions even in development or test servers ?. Are they DBAs ?. It appears that they all either want to be DBAs or they want to put in their resume that they can also do DBA. Is the DBA job that simple ?. I have been in an organization (My Last place. Started a new place and only has been one week but the same problem also exist here) where everyone (Not only developers but from the very upper management down to support people including the Veeam backup guy) seems to know everything about SQL Server (They think they know).

    They have added "Solution Architects into the DBA group in AD and gave the same permissions to all servers/instances as DBAs and they did this in a sneaky way without our knowledge but I found out soon enough to bring it up to the DBA management. When we have requested to get them out of the DBA group, we were declined (By a Director who was a friend of them). The DBAs seemed to be the bottom of the respected people list in this place and anyone would over power them with their requests (So I quit soon enough). What can be done in an organization to have others to recognize the real place of the DBAs and gain the respect they need ?. Yes, we are expert and extremely professional in our field and made so many changes to the organization from every angle (Performance, cost, reliability, less downtime e.t.c).

    The problem is, even they are "SysAdmins" on the SQL Server instances when they get in trouble with anything in SQL Server and can not resolve it, the first person they call is the Actual/Real DBA. In that case, I tell them since you are a SysAdmin on the instance I am sure you can fix it. Another case, they also have RDP access to the servers (Local Admin) but they call the DBA to reboot the servers. Why are they not capable of rebooting their servers ?

    I already hear some of the arguments but "We need to create our own databases", why ?. How often you need to create a database ?. Why can the DBA create it for you ?. If you need to reset the database you can delete all the objects in the database and start over again if you need to. You don't need to be a SysAdmin to do that. We (The DBAs will give you all the permissions you need - Except SysAdmin and even DBO - So that you don't accidently drop the database). If you are developing something that requires keep dropping and creating database(s), something is already wrong with your solution.

    I have seen so many people (Network engineers, Server & Storage people, developers, migration/deployment/IT support people, managers, directors even CIOs, CTOs) who are unqualified in their positions (Forget about been an expert in their fields) but yet they are holding such positions (Please don't ask me why I think they are unqualified. If I start listing, we will be here all day even all week) showing me the quality of the IT today which makes me think to quit IT altogether and open a Dollar store or something and relief myself from this headache.

    Thanks for any feedback and please let's not make this a lengthy conversation. Appreciate all the ideas and opinions in advance.

    • Moved by Tom Phillips Friday, October 26, 2018 1:35 PM Security question
    • Changed type DCarlos Friday, October 26, 2018 1:42 PM Not really a security question but a Roles and Responsibilies discussion
    Friday, October 26, 2018 1:29 PM

All replies

  • This is a very common discussion.  As a DBA/Developer I can argue both sides.

    Giving non-DBAs sysadmin rights stems from many historical things.  Until SQL 2008 there was no way to give some rights to non-sysadmin users.  This has somewhat been resolved with new rights, but there are still things non-sysadmin cannot do.  It really depends on the things they need to do.

    The main reason to give Devs sysadmin rights on non-production servers is to stop them from bugging the DBAs for every little thing, and having the DBAs be the bottle neck for development. 

    I see no reason "competent" Devs should not have sysadmin and local admin on Dev servers.  The Dev team should be fully responsible for the entire management of the dev servers.  This is how we have done it at my last 6 jobs.  It works fine, and rarely causes problem.  Dev systems are volatile and designed to be volatile.  If someone deletes something, too bad.  If someone reboots a server, too bad.   In some cases, we don't even backup user databases on "dev" servers.

    Friday, October 26, 2018 1:50 PM
  • What makes the developers to be qualified to do the DBA job ?. Besides my DBA expertise I also know some networking and server build/setup (Routers, switches, DNS, DCs, Monitoring e.t.c) as also involves my job as DBA (Server build from SQL Server installation standpoint, Networking from connection, data security, performance e.t.c standpoint) would that qualify me to be a Domain Admin ?
    Friday, October 26, 2018 1:56 PM
  • A dev server is a production server to a developer. Therefore, it should be treated like a production server. 

    Dev teams should be able to use tools like visual studio to do work locally, and then deploy changes. Often, being a member of db_owner is enough for them to accomplish 99% of their tasks.

    The line to draw is simple enough: database versus instance.

    Developers need access at the database level. Sysadmins need access at the instance level. 

    The only people that should be in the sysadmin role are people responsible for the management of the instance.


    Friday, October 26, 2018 2:13 PM
  • Agreed 100%.
    Friday, October 26, 2018 2:17 PM
  • Are your DBAs local administrators of the server SQL Server is installed on?  If so, why?  What makes you qualified to be a local administrator? Doesn't that give you rights to really mess up the server?  Who do you call when your server doesn't work?  There are things you cannot do in your job as a DBA without local administrator rights. 

    I do agree with SQLRockstar somewhat.  I agree 99% of things Devs need to do, do not require syadmin rights (anymore).

    Ideally, developers would do local development and have everything they need on their local machine, including SQL Server dev edition.  Then you do "integration testing" after that dev is compete and checked in, on a "non-dev" server, they do not sysadmin rights on.  That is not always possible when you have a 1TB database.

    The real question is, what do the devs need to do.  The answer to that question is not "everything" a DBA can do.  They need to list the specific things the devs need to do, and then you can solve that problem, instead of arguing over sysadmin vs non-sysadmin.

    Friday, October 26, 2018 2:34 PM
  • off-topic of the discussion, I liked this sentence "A dev server is a production server to a developer" :-)

    I might use it in the future...

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Friday, October 26, 2018 2:34 PM
  • "The real question is, what do the devs need to do.  The answer to that question is not "everything" a DBA can do.  They need to list the specific things the devs need to do, and then you can solve that problem, instead of arguing over sysadmin vs non-sysadmin."

    That depends on the Developer as to how good of a developer he/she is. Instead of deleting the data on the table or dropping and re-creating the table, you drop and re-create the database than we have issues. It is not an argument over sysadmin vs non-sysadmins. It is about roles and responsibilities of people with underlying permissions to the tools/resources they use. Let's respect each others roles and not cross the boundaries of our positions (Except when we struggle with people who are not expert in their fields. Example: Can't get the network engineers to setup the Multi subnet clusters from SQL HA standpoint on each side including in Cloud environment which requires different steps to be completed where DBAs don't have permissions to implement). Otherwise, the whole different IT departments will be in chaos.

    Friday, October 26, 2018 2:54 PM
  • Most companies do not have strict roles and responsibilities in IT, let alone for non-prod servers.

    Friday, October 26, 2018 6:43 PM
  • I would say that it depends on the requirements and situation of the organisation.

    In a shop where I've spent over 20 years, everyone knows the sa password to about all servers, and that is how you normally log into SQL Servers. You see, this is an ISV, so there are only dev and test instances. All production server are at customer sites.

    Do I need to have sysadmin? Hm, I've tried very much to be with out it, but I do need:

    • VIEW SERVER STATE (to access DMVs)
    • ALTER TRACE (to run traces to check performance etc.)
    • Permissions to create databases. (I often restore a copy of my dev database to a copy for special tests, etc)

    Couldn't I do this on my local machine? I do have an SQL Server instance on my own machine, but I think most people do not, and it would not be practical, because databases need to be maintained with upgrades to new versions etc. This system is a beast with 1700 tables and over 8000 stored procedures.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, October 26, 2018 9:53 PM
  • People other than DBAs can have developer edition on their laptop (Most companies pay for $50 or something license fee) and they can do what ever they want (It's their party. They can cry if they want to :)) but when it comes to company databases servers, I doubt they should be sysadmins. If they are and they also have RDP access with local admin permissions, as a DBA, I would not touch to that server. Hope they don't call me for anything they can't figure out. As far as the data is concern, No production data should be ever carried to another environment (May be other than Staging/Acceptence where the servers reside in the same production environment as they are and secure as they are). This has been violated in many places because people don't know how to work. I have always worked with financial companies and when this rule is crossed (WITHOUT proper permissions from the Security or Application owner e.t.c), I call the EFFIEC (Federal Financial Institutions Examination Council) because the people's private information (SSN#, Bank Account numbers, Phones, Addresses e.t.c) are exposed.

    Bottom line is not giving excessive permissions to people because they don't know how to work, teach/force them how to efficiently work with minimal permissions and become expert in their fields. As a DBA, I have done this to not only developers but also Network and Server & storage teams e.t.c. May be I did it wrong because they were able to find a better (Or better paying job) job and left the company they were working at. :). Over the years, have seen IT people not even capable of copying a simple file from one place to another, DBA who put 8 years experience in his resume but only exposed to Microsoft Access. These are why I am at the edge of quitting IT altogether.

    My Bad: SQL Server 2016-2017 Developer edition is Free.

    • Edited by DCarlos Friday, November 2, 2018 2:32 PM
    Friday, November 2, 2018 12:33 PM
  • People other than DBAs can have developer edition on their laptop (Most companies pay for $50 or something license fee) and they can do what ever they want (It's their party. They can cry if they want to :)) but when it comes to company databases servers, I doubt they should be sysadmins.

    Again: what works in one place, may not work in another. Working on a local instance of Dev Edition can work in some cases, but not if you are dependent of contributions from your colleagues.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, November 3, 2018 4:08 PM