How to use sqlcmd to grant database access

[ SQL  ]

11 Oct 2018

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:

Step-by-step instructions

  1. Connect to SQL server via RDP with your AD account, which does not have access to the SQL database

  2. 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
  3. Execute “sqlcmd” in the command prompt to start the SQL interactive shell.

  4. 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()

use *Database_name*

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

-- 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'