Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

How can i add a group to the sysadmin fixed server role in SQL Server 2008 R2?

See my related question for details step-by-step screenshots detailing showing that you're not allowed to add groups to the sysadmin role.

Background information

Before SQL Server 2008 R2, members of the local Administrators group were automatically added to the sysadmin fixed server role. Starting with SQL Server 2008 R2 that group is no longer added. The new recommended mechanism is to:

...create a separate Windows group containing the appropriate DBAs and grant that group the sysadmin role in the database.

How do i "grant a Windows group the sysadmin role"?

See also

Related questions

share|improve this question

1 Answer 1

up vote 4 down vote accepted

As stated, SQL 2008 R2 no longer adds the administrators group as a SQL login by default. You first have to add the Administrators group as a SQL Server principal.

CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS

(or see this article for more information on how to add a login)

You can then add that group to the sysadmin role.

sp_addsrvrolemember
    @LogiName='BUILTIN\Administrators',
    @RoleName='sysadmin'
share|improve this answer
    
Oi vay. i don't have edit permissions. Can you wrap [BUILTIN\Administrators] in [square brackets]. Otherwise you get incorrect syntax near '\' –  Ian Boyd Jan 4 '12 at 23:59
    
Oh, cripes. I did mean to put that in there.... Sorry boss! –  squillman Jan 5 '12 at 2:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.