Log in as another SQL Server user via SSMS to check their permissions?

Question

I am getting a little further into the database administration side of things and starting to set up security access to SQL server databases for users. I have a question:

Once I have given a set of permissions to an AD authenticated user via SSMS, is there a way I can 'log in' as that user to the SQL server to check and confirm that I've given them the correct privileges? I'm also logged in as an AD user with my own permissions so I wouldn't be able to log in as their AD account ID/password.

Thanks :)

Answer 1

You can run SSMS as a different user via runas /netonly as described here.

However for that method you would need to know the other user's password.

You can also use EXECUTE AS to impersonate that user from inside a query window logged in as you (as long as you have the required permissions to do so).

EXECUTE AS 'user_name';
/* Some SQL Statements */
REVERT;

Or you could adopt a policy of never granting permissions to users directly but always granting them to Windows groups or database roles. Then you just need to have another dummy user that you can grant the same role membership to.

• user27768: Thanks Martin, that's helpful. Is it fair to say then that, aside from the two methods you've described, there's no way for the grantor to 'quickly' log in as the login/user that they've created to impersonate and confirm the permissions they've granted are correct? (I'm thinking that this would be helpful for more tricky permission sets like SSIS package deployment etc - the grantor could quickly check all is good before notifying the database user that they have the correct permissions).
• Martin Smith: @user27768 yes, that's it. You can either actually log in as them possibly by running SSMS as a different user than you are logged in as or you can impersonate them. There is another way of impersonating using setuser but that is deprecated and has no advantage over exec as.
• Martin Smith: @user27768 or you could adopt a policy of never granting permissions to users directly but always granting them to Windows groups or database roles. Then you just need to have another dummy user that you can grant the same role membership to.
• user27768: Thanks Martin. Yes, I was thinking that in the absence of being able to log into SSMS as the specific user (because I don't have their AD password) that I could just ask to have a couple of dummy AD accounts or groups created that I could apply test permissions to and then check this by switching the dummy AD user via Windows/switch user. Or, like you mention, create a role and assign this role to a dummy SQL server login to check (which would mean I wouldn't have to log in/out of my own Windows session and could just delete dummy SQL server account afterwards). Interesting stuff! Thanks.

Answer 2

To do my process you need AD username and password. Keep'em handy. 1) Click on Start->All Programs->Microsoft SQL Server XXXX (Here XXXX is the Version of SQL Server you have). 2) Hold the Shift button and Right-Click on the application "SQL Server Management Studio". 3) Click on Run as different user. 4) Provide the AD username & password.

• user507: The OP specifically stated "wouldn't be able to log in as their AD account ID/password"
• Martin Smith: @ShawnMelton - But the reason they gave for that is because they are "logged in as an AD user with my own permissions" so presumably they aren't aware of the fact that applications can be run under different credentials from the current logged in user.