1

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 :)

Improve this question

2 Answers 2

Reset to default
4

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.

Improve this answer
4
  • 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).
    – user27768
    Commented Jun 19, 2016 at 9:32
  • @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
    Commented Jun 19, 2016 at 9:37
  • @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.
    – Martin Smith
    Commented Jun 19, 2016 at 9:49
  • 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.
    – user27768
    Commented Jun 19, 2016 at 10:04
-1

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.

Improve this answer
2
  • The OP specifically stated "wouldn't be able to log in as their AD account ID/password"
    – user507
    Commented Jun 19, 2016 at 10:53
  • @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.
    – Martin Smith
    Commented Jun 19, 2016 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.