Grant SA role from non-SA ID?

Question

Post reply

Grant SA role from non-SA ID?

chubbsm

SSCommitted

Points: 1770
More actions
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

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

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

sreeni pitti SSC Rookie Points: 33 More actions · Answer 1

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

chubbsm SSCommitted Points: 1770 More actions · Answer 2

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

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

Greg

chubbsm SSCommitted Points: 1770 More actions · Answer 4

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

Jeff Gray SSChampion Points: 10667 More actions · Answer 5

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

chubbsm SSCommitted Points: 1770 More actions · Answer 6

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!

Jeff Gray SSChampion Points: 10667 More actions · Answer 7

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

chubbsm SSCommitted Points: 1770 More actions · Answer 8

chubbsm

SSCommitted

Points: 1770

September 29, 2006 at 1:53 pm

#663456

Thanks!! I will check this out on Monday!

chubbsm SSCommitted Points: 1770 More actions · Answer 9

Just tried it - seems to work like a champ!

Thanks!!