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

I'm not a SQL admin, but I have rights to the SQL server granted from the old DBA that was with our company.

We have a new IT manager that wants DBA level permissions to our SQL servers and I don't know how to grant it to him.

POLITICS ASIDE as to whether it is a good idea to do this or not...

How can I grant DBA level (full) rights to a SQL server and all of its databases? Can I do it across all instances somehow?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

Method 1: Connect to the SQL server via SQL Server Management Studio, create a login for the manager under the security\logins folder, in the General properties select either Windows or SQL authentication and fill out the fields accordingly, select the Server Roles properties and select the sysadmin checkbox, click OK to close the new login window.

Method 2: Connect to the SQL server via SQL Server Management Studio. Ask the manager to come over and create a login for himself.

Note: Both methods assume you're running at least SQL Server 2005. If you're running SQL server 7 or 2000, let us know.

share|improve this answer
    
needs to be done for every instance on the server? Is there a way to create a "SQL DBAs" group in AD and then add them to a group on the server or something? –  TheCleaner Jul 20 '10 at 18:51
    
Yes, but basically on install. You can create an AD group, but you have to still log into each instance and map the group to dbo rights. –  TomTom Jul 20 '10 at 19:36

It might be faster to run this script on the servers instead of clicking your way out:

USE [master]
GO
CREATE LOGIN [YourLoginName] WITH PASSWORD=N'StrongPassword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF,
GO
EXEC master..sp_addsrvrolemember @loginame = N'YourLoginName', @rolename = N'sysadmin'
GO

Also if you are on SQL 2008, central management servers will allow you register many servers in a group and do Multiserver query execution of your queries/scripts.

share|improve this answer

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.