Creating a SQL Server login (windows account) without server admin rights

The default install configuration for SQL Server used to add the builtin\admin group to the server admin role, this is no longer the case with SQL Server 2008 and often causes the user installing SQL to be the only user that has any access to SQL.  Not usually an issue when you're doing your own install, but if someone else configures your developer machine(s) you can be a bit stuck.

It's been a while since I ran this, but if I recall correctly, here are the steps to get yourself added as a server admin.

1.  Shutdown SQL, this can be done in the SQL Server Configuration Manager or from the command prompt (if you have UAC enabled, you will need to run your command prompt with Administrative permissions).
  Example commands from technet:
  net stop MSSQLSERVER  or net stop MSSQL$instancename
  http://technet.microsoft.com/en-us/library/ms190236.aspx

2.  Start an instance of SQL running under your current windows user account, replace <MachineName> with computer name found under system properties and <InstanceName> with the name of your SQL instance.  If you're using the default install settings, MSSQL is the default (SQLEXPRESS if using the express version of SQL).

This is the location of my SQL executable (and therefore where I opened my command prompt to): C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn>
In a command prompt (path set to MSSQL\Binn directory), enter:
sqlservr.exe -S <MachineName>\<InstanceName> -E

If successful, you should get a 'SQL Server' popup/dialog with an OK button, do NOT click OK until we are finished with our 2nd command prompt.3.  With the command prompt still open for step 2, open a new command prompt, and execute the following script/command.  Note: I'm using 'WEBERNET' for my domain and 'TWEBER' for my user in this example.  I have not attempted using this script outside of a domain, but my guess is that the domain can simply be replaced with the computer name if the computer or user is not part of a domain.

sqlcmd -S <MachineName>\<InstanceName> -E

CREATE LOGIN [WEBERNET\TWEBER] FROM WINDOWS
GO

exec sp_addsrvrolemember @loginame='WEBERNET\TWEBER', @ROLENAME='sysadmin'
go

4.  And we're done, go ahead and click OK on the SQL dialog and start SQL again as normal (net start MSSQL$SQLEXPRESS).

Microsoft does have a script available for doing this same thing on SQL Express, I haven't tried it out yet though nor do I know if it also works with the standard SQL install.

http://archive.msdn.microsoft.com/addselftosqlsysadmin/