How to use sqlcmd to grant database access

Suppose you have an IT system which hosts its database on an SQL server instance and you know the service account AD login and password. If your personal admin account does not have access to the SQL server database, you can give yourself permissions to this database using the instructions below.

There are a few notes:

You have to be able to connect to the SQL server via RDP with OS admin privileges.
The service account must be dbowner for this method to work, which is usually the case.
There is an alternative method which involves SQL instance restart with a special command line option.

Step-by-step instructions

Connect to SQL server via RDP with your AD account, which does not have access to the SQL database
Start command prompt with the credentials of the service account that has access to the database and enter the password for the service account when prompted:
```
runas.exe /user:domain\service_account_name cmd.exe
```
Execute “sqlcmd” in the command prompt to start the SQL interactive shell.
Execute these TSQL commands in a sequence:

-- Check the current user name - you must be running sqlcmd under the service account credentials
select suser_sname()
go

use *Database_name*
go

create user [domain\your_login_name] for login [domain\your_login_name]
go


-- Add your login to the 'db_datareader' SQL database role in this example to grant read access to the data
exec sp_addrolemember 'db_datareader', 'domain\your_login_name'
go