Search code examples
sqlwindows-authenticationroles

SQL Windows Authentication Roles?


I have read a lot, but still i can't find the point that i want which is the following:

If i can connect to sql via windows authentication mode, then that mean after i install my software with it's database the user can easily look and manipulate my database, and if i want to revoke any role i will be limiting my program when it's going to access the database.

Is there a way to limit the user access on the database while my program can have a full access without any problems.


Solution

  • The application doesn't have to login to sql using the account that it's being run from. So create a seperate user account for the application that has all the rights it needs and login using that account from the application.

    Alternatively, you can just setup a seperate login using sql server authentication and then you won't need another user account.

    If this isn't your application and you can't modify it to use a different account to login with, then you could run the application itself under another account. When you hold shift and right click an icon you'll see it gives you an option to do so. However, I don't know how you would set the application up to automatically run that way without the user having to know the password to type in. I think it's possible though.

    Also, I think when you set the account up you can set it as a special type that users can't actually login with. So they could know the password to it to run the application, but they wouldn't be able to actually login under that account to do anything with it. This wouldn't prevent someone smart enough from gaining access, but it's a good safeguard.