Post reply

Grant SA role from non-SA ID?

  • September 28, 2006 at 10:38 am

    #103247

    Hello - Does anyone know of a way to grant the SA role to a SQL ID without being a member of SA?

    The scenario is we are outsourcing some of our production support and will need the "out-source" DBA's to have SA role in order to support these instnaces. The problem is is that a new process will be created in which a security operations team will be the ones granting the "out-source" DBA the SA role. Is there anyway that the operations team can have the ability to grant SA role without actually being part of SA?

    I was looking at giving them the securityadmin fixed server role but this doesn't have the ability to grant SA. Looks like we are stuck with garnting SA role to the security group so they can do the same. Does anyone have an idea's on this?

    Thanks,

    -Mike

  • September 28, 2006 at 11:26 am

    #663093

    IF YOUR SERVER GOT 'BUILTIN\ADMINISTRATORS' server admin people can grant the sa rights

  • September 28, 2006 at 12:02 pm

    #663104

    Thanks - but part of our security lockdown is to remove Builtin as part of the initial server install.

  • September 28, 2006 at 2:29 pm

    #663158

    Only a member of sysadmin can add a member to sysadmin.

    Greg

    Greg

  • September 28, 2006 at 2:50 pm

    #663163

    Thanks - that's what I was afraid of! All my testing pointed that way!!

  • September 29, 2006 at 12:46 pm

    #663436

    Hello,

    Nice idea, except that they can just grant themselves sa.

    There are a few ways that you can work around this.  Probably the most simple would be to get an agent to do it for you by proxy.  One such agent would be the SQL Server Agent.  Assuming that SQL Agent runs under an sa equivalent account, you can get it to do your bidding.

    I suppose that you could create a table to hold the details on the account that needs to be granted sa rights.  Once a minute, the SQL agent (via a scheduled job) reads the table and if it finds a row in the table, it grants the rights as requested, then removes the row from the table.

    Grant only insert permissions on this table (via a stored procedure, of course) to the role that will assign sa permissions.  You should probably code a way to prevent them from granting themselves sa permission while you're at it.

    hth jg

     

     

  • September 29, 2006 at 12:56 pm

    #663441

    Thanks!!

    Re: >>Nice idea, except that they can just grant themselves sa.

    Supposed to be the honor system for the operations people not granting themslves SA!

  • September 29, 2006 at 1:44 pm

    #663454

    Another option would be to give them the ability to update the xstatus column in master.dbo.sysxlogins

    type

    sp_helptext sp_addsrvrolemember

    in QA with results to text turned on.  That is the code that actually gives sysadmin permisisons to a user.  You could probably just issue the the ...update sysxlogins set xstatus=... part and then  exec('use master grant all to null') without doing all of the other steps. 

    Then the user would only need update permissions on master..sysxlogins.

    I'm not trying it in PROD to see if it works, and I'm not connected to TEST today...

    hth jg

     

  • September 29, 2006 at 1:53 pm

    #663456

    Thanks!! I will check this out on Monday!

  • September 29, 2006 at 2:20 pm

    #663464

    Just tried it - seems to work like a champ!

     

    Thanks!!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply