I am having trouble with the following scenario:
Windows Server 2008 Enterprise Edition, SQL Server 2008 Enterprise Edition, and Team Foundation Server all running on one machine.
I am trying to create a maintenance plan to backup the TFS databases. In order to do that I have to be able to work in the SQL Management Studio with an account that is a member of the "sysadmin" server role.
The only member of the "sysadmin" server role is the "SA" account. The "SA" account password was left blank during install. When I try to add a new login (my domain admin account in this case) to the "sysadmin" server role I recieve the error message noted in "ERROR MESSAGE 1" below.
If I try to log into the SQL Management Studio with the SA account via the SQL Authentication mode I receive the error message noted in "ERROR MESSAGE 2" below.
My goal is to either:
(A) Add another login to the "sysadmin" role
(B) Login to the SQL Management Studio with the "SA" account
Please help! Thank you.
ERROR MESSAGE 1:
Add member failed for ServerRole 'sysadmin'. (Microsoft SqlServer.Smo).
|_ An Exception occurred while executing a Transact-SQL statement or batch.
|_ User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247)
ERROR MESSAGE 2:
Cannot connect to <INSTANCENAME>
|_ Login failed for user 'SA'. (Microsoft SQL Server, Error: 18456)