locked
How to give sysadmin rights without giving rights to one database? RRS feed

  • Question

  • We have a database I am building an application on.  But I can't kill processes or do traces because they don't want me to access one of the databases on the server.  Is there any way I can get rights sysadmin type rights so I can do traces and manage processes on my database without getting access to the confidential database?  Thanks


    Monday, August 11, 2008 8:33 PM

Answers

  •  In many cases it is not necessary to have full sysadmin privielges, I would recommend reviewing the minimum priviles for any operation in BOL and follow the least-privielge principle. Even in cases where the required privilege is too high, it is possible to wrap it around a signed T-SQL module and allow a controlled elevation of privileges via the signature.

     

     BOL errata note: To kill a session in the latest version of SQL Server 2005 it is sufficient to have "ALTER ANY CONNECTION" permission, but this change is not reflected in BOL yet, I apologize for the inconvenience.

     

      A few links that may be helpful:

    * http://blogs.msdn.com/lcris/archive/2005/06/15/sql-server-2005-procedure-signing-demo.aspx

    * Module Signing (http://msdn.microsoft.com/en-us/library/ms345102.aspx)

    * Signing stored procedures in SQL Server (ADO .Net) (http://msdn.microsoft.com/en-us/library/bb669102.aspx)

     

    I hope this information helps,

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Tuesday, August 12, 2008 12:06 AM

All replies

  •  In many cases it is not necessary to have full sysadmin privielges, I would recommend reviewing the minimum priviles for any operation in BOL and follow the least-privielge principle. Even in cases where the required privilege is too high, it is possible to wrap it around a signed T-SQL module and allow a controlled elevation of privileges via the signature.

     

     BOL errata note: To kill a session in the latest version of SQL Server 2005 it is sufficient to have "ALTER ANY CONNECTION" permission, but this change is not reflected in BOL yet, I apologize for the inconvenience.

     

      A few links that may be helpful:

    * http://blogs.msdn.com/lcris/archive/2005/06/15/sql-server-2005-procedure-signing-demo.aspx

    * Module Signing (http://msdn.microsoft.com/en-us/library/ms345102.aspx)

    * Signing stored procedures in SQL Server (ADO .Net) (http://msdn.microsoft.com/en-us/library/bb669102.aspx)

     

    I hope this information helps,

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Tuesday, August 12, 2008 12:06 AM
  • how about fixed role "processadmin" ?
    Tuesday, August 12, 2008 6:53 AM
  • Kill Processes
    Check out "Permissions of Fixed Server Roles".

    The fixed server role "processadmin" maps to the more specific permissions "ALTER ANY CONNECTION" and "ALTER SERVER STATE". As Raul stated above, the permission required for killing processes is "ALTER ANY CONNECTION", so granting processadmin would grant more rights than needed.
    Run Profiler
    Grant "ALTER TRACE" to allow a user to run Profiler.

    Reference: Permissions Required to Run SQL Server Profiler

    • Proposed as answer by rgarrison Tuesday, March 3, 2009 6:45 PM
    Tuesday, March 3, 2009 6:44 PM