Remotely adding AD group to SQL local “sysadmin” role

55 pts.
Tags:
SQL Server
I am attempting to run the script below via MS SQL server 2005 management studio (MS) connecting remotely to SQL 2000 Enterprise Ed. servers. I have all of my remote SQL servers registered in MS, and choose "new query" while server is highlited. I then run this script: Use master GO EXEC sp_grantlogin 'domainnamegroupname1' EXEC sp_addrolemember 'sysadmin', 'domainnamegroupname1' GO ... and get this error: Granted login access to 'domainnamegroupname1'. Msg 15014, Level 16, State 1, Procedure sp_addrolemember, Line 37 The role 'sysadmin' does not exist in the current database. I've tried the script without the "use master" option, but have not tried setting to different db's. I want this AD group to have full admin access to everything SQL on these servers ... can someone give some guidance? I perused this link (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_addp_4boy.asp), but it does not give any info about Server-level roles, only db-level roles. Thanks!
ASKED: April 4, 2006  4:07 PM
UPDATED: April 5, 2006  10:01 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

You cannot add a Windows NT group to a fixed server role. You can only add a SQL Server User or an NT User Account to a fixed server role. See sp_addsrvrolemember. The entry is two down in the link you included with your post.

Kevin

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • DT2115
    Thanks Kevin! Guess I didn't catch that when perusing the page. So if I understand correctly, there is no way to add an AD group to a fixed server role - you have to do it via individual ID's? Seems pretty hoaky that MS SQL srv would let you add MS AD groups!
    55 pointsBadges:
    report
  • KingConan
    No problem. They let you add groups to database roles for issues like development teams and such. Because of the power of the fixed server roles they only allow you to add individual logins. It would be a major security issue to allow blanket logins this kind of access. One of the reasons they do this is because once you have system admin rights in SQL Server, you can compromise the domain by granting yourself administrative rights in said domain as well via things like xp_cmdshell. This, in many organizations, results in auditing and security issues. For example, if you have joe user, who is a member of a group that has administrative rights via a fixed server role at the SQL Server, you've just opened this guy up to have administrative rights in the domain IF he knows what he is doing. Kevin
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following