|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Granting SA rights to Sr. Developers in Small ITS Groups when theror Best Practices - and I could use some advise. Our ITS group is too small to have a full time [application] DBA for SQL Servers so only Networking has SA rights on the servers which in the past was not a problem. But now that we're starting to develop/implement new Coldfusion and SQL Server Database applications as we head toward switching from static HTML to a dynamic database driven multiple server production environment that will use both a staging and a development server. Since these are new technologies to this environment and the production servers that only contain public information (i.e. do not contain high risk data), Development has requested read-only access to the production and staging servers to look at the data integrity, check the rev. number on the code, and look at IIS, CF, and SQL logs and configuration files, as needed. Development has also requested SA rights for their Sr. Developers only on the development server (i.e not the product servers) to be used for testing replication, running diagnostics, reviewing logs, create test applications users/roles to test permissions, test configuration settings and permission issues (we are also implementing active directory) but access was denied to both requests. With one month left before going live with the new production servers on IIS/CF/SQL, development is concerned with the time delays with testing or replicating problems on the development server and the inability to diagnose production problems with outdated logs (vs real time monitors of IIS/CF). The networking staff are competent at network issue but are not DBAs and doe not have knowledge of how setting and permissions in SQL Server (not to mention IIS or CF) will affect applications. How should SQL Server roles/permission be split between Networking and Development on the Development server in our small environment (note: our Sr Developers have 5 - 25 years of development experience including commercial DB engineering)? hi
there are no hard and fast rules to set a role to the users of the database. it depends on the policy that u draft. U need not have a full time DBA, eventhough its advised to have. Senior developers with good admin skills can take up the role. the person should have knowledge on taking backups and crash recovery hope this answers the question -- Show quotebest Regards, Chandra http://chanduas.blogspot.com/ http://groups.msn.com/SQLResource/ --------------------------------------- "JA" wrote: > I just finished reading several articles on the SQL Server Security Checklist > or Best Practices - and I could use some advise. Our ITS group is too small > to have a full time [application] DBA for SQL Servers so only Networking has > SA rights on the servers which in the past was not a problem. But now that > we're starting to develop/implement new Coldfusion and SQL Server Database > applications as we head toward switching from static HTML to a dynamic > database driven multiple server production environment that will use both a > staging and a development server. Since these are new technologies to this > environment and the production servers that only contain public information > (i.e. do not contain high risk data), Development has requested read-only > access to the production and staging servers to look at the data integrity, > check the rev. number on the code, and look at IIS, CF, and SQL logs and > configuration files, as needed. Development has also requested SA rights for > their Sr. Developers only on the development server (i.e not the product > servers) to be used for testing replication, running diagnostics, reviewing > logs, create test applications users/roles to test permissions, test > configuration settings and permission issues (we are also implementing active > directory) but access was denied to both requests. With one month left > before going live with the new production servers on IIS/CF/SQL, development > is concerned with the time delays with testing or replicating problems on the > development server and the inability to diagnose production problems with > outdated logs (vs real time monitors of IIS/CF). > The networking staff are competent at network issue but are not DBAs and doe > not have knowledge of how setting and permissions in SQL Server (not to > mention IIS or CF) will affect applications. How should SQL Server > roles/permission be split between Networking and Development on the > Development server in our small environment (note: our Sr Developers have 5 - > 25 years of development experience including commercial DB engineering)? It sounds like you're between a political rock and a hard place. The
important thing is to save in your CYA file a copy of the memo informing management that what's going live cannot be adequately tested without access, and since access has been denied, that they'll have to live with the possibility of a meltdown. Once a meltdown occurs, you can be absolutely sure that you will be given all the access you need (and probably more) in order to fix the problem. (You might want to plan on an all-night debugging fest, so be sure to stock up on Mountain Dew.) Another solution would be to request equipment identical to the production environment that is dedicated and accessible to the ITS group just for testing. Of course someone will have to pay for the new equipment, and you'll probably have to delay going live until the new equipment is configured and testing is completed. Show quote "JA" <J*@discussions.microsoft.com> wrote in message news:9E08BED2-6BEA-46B7-966E-64A6ADE1AD19@microsoft.com... > I just finished reading several articles on the SQL Server Security Checklist > or Best Practices - and I could use some advise. Our ITS group is too small > to have a full time [application] DBA for SQL Servers so only Networking has > SA rights on the servers which in the past was not a problem. But now that > we're starting to develop/implement new Coldfusion and SQL Server Database > applications as we head toward switching from static HTML to a dynamic > database driven multiple server production environment that will use both a > staging and a development server. Since these are new technologies to this > environment and the production servers that only contain public information > (i.e. do not contain high risk data), Development has requested read-only > access to the production and staging servers to look at the data integrity, > check the rev. number on the code, and look at IIS, CF, and SQL logs and > configuration files, as needed. Development has also requested SA rights for > their Sr. Developers only on the development server (i.e not the product > servers) to be used for testing replication, running diagnostics, reviewing > logs, create test applications users/roles to test permissions, test > configuration settings and permission issues (we are also implementing active > directory) but access was denied to both requests. With one month left > before going live with the new production servers on IIS/CF/SQL, development > is concerned with the time delays with testing or replicating problems on the > development server and the inability to diagnose production problems with > outdated logs (vs real time monitors of IIS/CF). > The networking staff are competent at network issue but are not DBAs and doe > not have knowledge of how setting and permissions in SQL Server (not to > mention IIS or CF) will affect applications. How should SQL Server > roles/permission be split between Networking and Development on the > Development server in our small environment (note: our Sr Developers have 5 - > 25 years of development experience including commercial DB engineering)? SA rights on a development box does not sound unreasonable; in fact, I
recommend it, for many of the reasons you list above. Developers need their own space to break stuff, so that they understand how NOT to break it in production. I work in a small shop as well, and currently I serve as both production and development DBA; we're hiring a production DBA, but until then I have to document every little thing I do to make sure that there is a record for accountability depending on the hat I'm wearing. I think it's OK for developers to have read-only access to llimited-risk data in production, and they should have full control over their development environment. And you can tell your bosses I said so :) Someone on the Interwebs believes in your cause. Stu On Fri, 29 Jul 2005 22:56:02 -0700, JA wrote:
(snip) >The networking staff are competent at network issue but are not DBAs and doe Hi JA,>not have knowledge of how setting and permissions in SQL Server (not to >mention IIS or CF) will affect applications. How should SQL Server >roles/permission be split between Networking and Development on the >Development server in our small environment (note: our Sr Developers have 5 - >25 years of development experience including commercial DB engineering)? I tend to agree with Stu: SA rights on the dev. server for experienced DB developers and read rights to non-sensitive stuff on the prod server sounds reasonable. On the other hand, the networking staff has been assigned the responsibility to keep the databases up and running and to protect data from from unauthorized spreading (I'm sure that this is a terrible sentence, but I don't know how to put it in correct English - I hope you do understand what I'm trying to say). And since it's THEIR responsibility, they are free to take whatever measures THEY think are needed to accomplish that goals. Many years ago, I had to do some major reshuffling on a SQL Server implementation. The sa access I had was enough for about half the things I needed to do (like shutting down the service, or creating a new file), but many other tasks (like restarting the service, and deleting files no longer needed) were only available for the network admins. Of course, I requested access as network admin, to streamline the process. And of course, I didn't get it. So I phoned the network admins each time I needed them to do something: "Hi, Hugo here - could you restart the SQL Server service please?" "Hi, Hugo here - could you restart the SQL Server service please?" "Hi, Hugo here - could you delete file XXX on server YYY please?" "Hi, Hugo here - could you restart the SQL Server service please?" "Hi, Hugo here - file AAA has to be moved from drive BBB to CCC." "Hi, Hugo here - could you restart the SQL Server service please?" "Hi, Hugo here - could you restart the SQL Server service please?" "Hi, Hugo here - I requested a restart of the SQL Server service some 5 minutes ago, but doesn't seem to be running" And so on. Guess how long it took before I DID get some extra rights? Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) On 30 Jul 2005 16:38:27 -0700, Stu wrote:
>When I worked help desk, we used to hate guys like you :) Hey! I was only trying to get my job done, you know!(mumble, whine, mumble) Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)  |
|||||||||||||||||||||||
Other interesting topics