2

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?

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.

  • 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
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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