After inheriting an SQL server with an already revoked SQL administrator account I needed to take ownership of the DB that the previous SQL admin had access to. So I had admin access to the server, but I needed sysadmin access to the SQL Management tool and the databases within it.

Step 1:
Stop the SQL server instance. To do so goto SQL server configuration manager, select SQL Server Services, then on the right right click SQL Server and STOP the service.

Step 2:
Open an administrative cmd prompt and start SQL in single user mode. You need to ensure that you are in the correct folder to execute the command. The SQLservr.exe is commonly found in:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn

(But it depends on your version of SQL – just look for SQLservr.exe)

Step 3:
Enter the following command to ensure you start SQL in single user mode:
sqlservr -sSQLINSTANCENAME -m
(You should see the services being started in the cmd window – ALSO NOTE the INSTANCENAME is YOUR SQL server’s instance name so please replace accordingly)

Step 4:
Open a new administrative CMD prompt and, depending on your version of SQL, enter the following:

create login [<<DOMAIN\USERNAME>>] from windows;
-- For older versions of SQL Server:
EXEC sys.sp_addsrvrolemember @loginame = N'<<DOMAIN\USERNAME>>', @rolename = N'sysadmin';

-- For newer versions of SQL Server:
ALTER SERVER ROLE [sysadmin] ADD MEMBER [<<DOMAIN\USERNAME>>]

GO

Now kill both CMD sessions and open up SQL Management Studio as Administrator. You should now be a sysadmin and can then give yourself access to your desired database.

Source:
https://stackoverflow.com/questions/9889334/how-do-i-grant-myself-admin-access-to-a-local-sql-server-instance

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s