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:
EXEC sp_grantlogin 'domainnamegroupname1'
EXEC sp_addrolemember 'sysadmin', 'domainnamegroupname1'
... 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.
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!