If you’ve ever set up SQL Server for TFS, you know the SQL Server set up wizard asks you to specify administrators for SQL Server (pictured). You can add your own user account here and you’ll be the only administrator added to the SQL Server admin group. If you want to be the only one who has access to this group, that’s fine. But if you’re using a disposable test accounts or multiple accounts for any reason, you may find yourself locked out of SQL Server.
I like to add the local administrators group on this page. If you add the local administrators group, all users who are in the local administrators group are also in the SQL Server admin group.
Here is how you do it.
1) Click Add. If you’re in a Workgroup, skip to step 4. If you’re in an AD domain, there are a few more steps.
2) In the box that pops up, click Locations.
3) In the next box that opens, click the local computer at the top of the tree.
4) Back in the original box, type in the name of the computer, a backslash, and Administrators and then OK. You can see below that the name of my computer is the same as the node at the top of the locations list.
5) The local administrators group will appear in the SQL Server administrator’s box. Make sure you don’t spell administrator (no s), or you’ll add the local administrator account instead of the group. If you’re setting up reporting for TFS, you can do this same procedure for Analysis Services admin access after you click next. By setting up SQL Server this way, local administrators will also have administrative access to SQL Server and Analysis services.