Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How do i meet developer's request without grant Sysadmin privileges

Jawab How do i meet developer's request without grant Sysadmin privileges

  • Tuesday, September 25, 2012 10:44 PM
     
     
    Sign In to Vote
    0

    A developer has asked for the follow permission in a Dev shared SQL Server 2008 R2 environment. Is there a way to meet developers request without granting him "SysAdmin" privileges. Since there is a shared environment with other application databases on the same server.

    Able to run SQL server tuning advisor

    View deadlock information; kill process if long running/blocking/deadblocking

    View SQL server log, View all processes, view performance related DMV

    Able to run DBCC checkdb

    View SQL server log, View all processes, view performance related DMV 

    Regards

    Wagiss-


    • Moved by Tom Phillips Wednesday, September 26, 2012 1:52 PM Security question (From:SQL Server Database Engine)
    •  
     

All Replies

  • Wednesday, September 26, 2012 12:26 AM
     
     
    Sign In to Vote
    0

    1.  It's dev, so just give him SA.

    2.  If you have to ask, give him SA.

    3.  Not sure, but you can certainly come close by granting him all the separate operator and view privileges, but it's more work than it's worth, since tomorrow he'll probably want the last two or three MORE things that *still* require SA.

    Josh

     
  • Wednesday, September 26, 2012 2:17 AM
     
     Answered
    Sign In to Vote
    0

    All the information you provided those looks different types of permission requires at the server or database level -how ever since it is dev server if you are ok to give SA then there will be no issues but overall it looks Sa incase if all the things requires for the better option but ensure the impact as well, how ever please see below-

    A)DBCC CHECKDB
    =============
    Permissions

    Requires membership in the sysadmin fixed server role or the db_owner fixed database role.
    http://msdn.microsoft.com/en-us/library/ms176064.aspx

    B)Able to run SQL server tuning advisor-
    Permissions Required to Run Database Engine Tuning Advisor
    http://msdn.microsoft.com/en-us/library/ms190987%28v=SQL.100%29.aspx

    C)View deadlock information;
    you can easily check in the errorlog if the trace flag enalbed it the startup parmetrs i,e 1022/1024(to capture the deadlcok information)-
    so if he is accesses for errorlog then he can use that one or if he has accesses to view the prfolier to view(Incase it is ran manuualy)..

    D)kill process if long running/blocking/deadblocking-
    Permissions
    Requires the ALTER ANY CONNECTION permission. ALTER ANY CONNECTION is included with membership in the sysadmin or processadmin fixed server roles.
    http://msdn.microsoft.com/en-us/library/ms173730.aspx


    E)View SQL server log
    see-http://www.sqlservercentral.com/Forums/Topic939436-359-1.aspx

    F)View all processes
    sys.sysprocesses (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms179881.aspx

    Open Activity Monitor (SQL Server Management Studio)
    http://msdn.microsoft.com/en-us/library/ms175518.aspx


    G)view performance related DMV-
    There are two types of dynamic management views and functions:

        Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.

        Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.

    Dynamic Management Views and Functions (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms188754.aspx

    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.


     
  • Wednesday, September 26, 2012 8:33 AM
     
     
    Sign In to Vote
    0

    Thanks Udaya.. its really help me a lot

    Thnx

    Mak

     
  • Wednesday, September 26, 2012 2:54 PM
     
     
    Sign In to Vote
    0

    Hi,

    IMHO, a developer shouldn't be viewing SQL Server Log, running DBCC CHECKDB, monitoring server processes, killing processes and following-up deadlocks.

    Those are all DBAs tasks.

    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

     
  • Wednesday, September 26, 2012 3:30 PM
     
     
    Sign In to Vote
    0

    Hallo Sebastian,

    this it not true! Why should a developer not have the rights to check his own coding and it's results in the db?

    ...  permission in a Dev shared SQL Server 2008 R2 environment. ...

    If it is a shared environment (Multi-Instances) the only thing which need to be considered is the privileges of the service account under which the db engine is running (e.g. because of usage of xp_cmdshell!)

    Otherwise the developers should ALWAYS have full rights to the DEV-box!

    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de

     
  • Wednesday, September 26, 2012 3:44 PM
     
     
    Sign In to Vote
    0
    nanaasare: You might be interested in this chart that lists all the permissions, and at least some of the statements that they affect.
    http://go.microsoft.com/fwlink/?LinkId=229142

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

     
  • Wednesday, September 26, 2012 3:47 PM
     
     
    Sign In to Vote
    0

    Hi,

    In a perfect world, I'd agree with you.

    I'm not against having users monitoring their own processes, but being a sysadmin comes with a lot of power.

    Ater 20 years of experience, I learned that people with sysdamin privileges (including ourselves) may -and eventually will- cause major disruptions

    like a DROP DATABASE in the middle of the night.

    "Oops! I dropped db_Sales instead of db_Sandbox..." so guess who's going to be paged at 2:00 AM ;-)

    In some companies and projects, losing a development DB may turn into a major crisis, specially during upgrades or company-wide changes.

    My suggestions :

    1. Write scripts, view, stored procedures or reports exposing just the data and methods that our users (and ourselves) need.

    2. Avoid logging as a sysadmin as much as possible

    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

     
  • Thursday, September 27, 2012 7:06 AM
     
     
    Sign In to Vote
    0

    Hallo Sebastian,

    independent from the request of the TE I disagree with you. A developer should ALWAYS have FULL access to his sql box if it...

    - is a dedicated environment (e.g. VM)
    - is working with a local account as service account
    - may be in a different domain / subnet

    From my point of view (as developer AND dba) it is an imperative issue!
    From my view as a dba I get scared (as you do) but with a good designed dev environment I can sleep pretty fine

    From my view as a developer I have complete different requirements. I want to trace my procs, log any long running queries, want analyze the performance of my sql box. I want to check the performance of the IO-Subsystem with SQLIO, I want to see the using of resources.

    All developers in our Bank have to sign a document which defines no operational support by the dba; this include backups and any other support.
    The developer should be familiar enough with the system and should be familiar with the security policy of his employer, too!

    So, if he drops a database and does not have a backup...

    don't worry, he will do it only once! :)

    We as dba have to guarantee a concept which prevents concurrent usage of an instance / VM by different business areas and I have to define an environment which blocks any access from a dev environment to an UAT (pre-prod) and a PROD / DR. If the developer would like to have prod data he has to raise a respective request by a ticket system. We provide a backup file with anonymized data.

    That's our job as dba, nothing else!

    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de

     
  • Thursday, September 27, 2012 12:01 PM
     
     
    Sign In to Vote
    0

    Thank you for your feedback, I appreciate it.

    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu