As a SQL server DBA many times you may get to hear from users that he is not able to access the database, SQL server or any of the objects in SQL server. This may sometimes that the user is passing a wrong credentials or may be is trying to connect to SQL server through some applications but due to some ODBC / OLEDB connectivity problem the application is not able to connect even though user is passing correct credentials. Sometimes the issue may be genuine too that he is actually not having the access. So as a DBA what we can take up as a initial troubleshooting is to Impersonating the SQL user account to check whether is user having access to database or not. So below mentioned are the steps to be carried out.
Step1. Connect to SQL Server with Sysadmin Privilege account ( Normally DBA’s will be having SysAdmin Privilege)
Step2. Select a New Query from Menu and go to Database the user is trying to access using below command.
Step3. Now Impersonate the account using below SQL query and check for the status.
EXECUTE AS USER = ‘YOURDOMAIN\ACCOUNTNAME’
If the user account is invalid you will get the below Error:
Msg 15404, Level 16, State 11, Line 1
Could not obtain information about Windows NT group/user ‘YOURDOMAIN\ACCOUNTNAME’, error code 0x534.
Else if you get the output as “command (s) completed successfully” then check for the access using below command.
Now the above query will give you username and access.
If you want to check for specific permission you can do so by using below command. If the result set returns 1 means he is a member of that permission for example I have taken db_owner in below example. If not it will return you 0.
Step 4. Now run REVERT to go back to execution from your credentials.
The above process can generally be used when testing a database user’s permission. Hope this is of some help. Thank you.