Post reply

SQL Login with two different permissions

  • September 19, 2018 at 11:38 am

    #377437

    Hi All,
    I got requirement from application developer team for create user for access database.
    Lets say 'domain\dbuser', this account used by 2 guys A and B

    Requirement is like this,
    if A is using this account while it should do everything on DB like db_owner permissions, but if B is using this account it should do everything but can not create table on database.

    Can single login carry two permissions like above?

    How can I achieve above requirement, doI have to create two different accounts and grant every other permissions and deny create table for guy B?

    Thanks in advance..!

  • September 19, 2018 at 11:41 am

    #2006318

    Shree_L - Wednesday, September 19, 2018 11:38 AM

    Hi All,
    I got requirement from application developer team for create user for access database.
    Lets say 'domain\dbuser', this account used by 2 guys A and B

    Requirement is like this,
    if A is using this account while it should do everything on DB like db_owner permissions, but if B is using this account it should do everything but can not create table on database.

    Can single login carry two permissions like above?

    How can I achieve above requirement, doI have to create two different accounts and grant every other permissions and deny create table for guy B?

    Thanks in advance..!

    If it is a joint user account, how would it know who was using it?  Bad idea.  As you appear to be using windows accounts each user should have their own account to log in to the database.

  • September 19, 2018 at 12:22 pm

    #2006324

    There is an object called an Application Role that has a password. The idea is to grant permissions to the Application Role and in the application connect with the login's credentials, run the sp_setapprole stored procedure with a password known only to the application. With that configuration the login could have different permissions using the application than logging in with SSMS. You can also use execute as and grant the user impersonate on a differently privileged user.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply