3

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?

Improve this question

2 Answers 2

Reset to default
4

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.

Improve this answer
2
  • 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? Commented Jul 20, 2010 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. Commented Jul 20, 2010 at 19:36
5

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.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.