Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Posts
    1,245

    Unanswered: Help with Security!

    I have a web application for which I am required to authenticate users at the database level (no generic or application type logins permitted). I am not permitted to use Active Directory because we do not have AD installed. We chose to use standard SQL accounts. I have two groups of users:

    1. Normal users
    2. Super Users (can do everything a normal user can do, plus can add/delete/modify user accounts)

    When a Super User is created, they are added to three fixed roles Security Administrator (Server Role) and db_accessadmin and db_securityadmin (Database Roles).

    A normal user is assigned to some custom roles that we created, but is not assigned to any fixed roles (database or server) other than the default Public role.

    The problem comes when a Super User attempt to add another Super user. The process fails because the Super user does not have sufficient privileges to run sp_addrolemember. The following two statements fail because of permissions:

    sp_addrolemember 'db_securityadmin', N'mySuperUser'
    sp_addrolemember 'db_accessadmin', N'mySuperUser'

    Additional research indicates that I am required to be a member of the SysAdmin fixed role of the db_Owner role in order to have access to sp_addrolemember.

    Does anyone have any suggestions for a workaround? This is pretty frustrating. I am unwilling to let my Super Users have sysadmin or db_owner rights. These grant far more access than is needed. I just want my super users to be able to add and administer normal user accounts and other Super User accounts.

    Thanks,

    Hugh Scott

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I think you are using SQL2K, if so how about using Application roles? I have not done this before but it might be worth checking out. Look up Application Roles in BOL.

    My theory is that you could setup an application role with dbo authority. When you need to create a superuser you would make an additional connection to the db using an application role, create the super user and then drop the connection.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Ding!

    You are correct. I should have stated that we were using SQL 2K. I like your idea and I will give it a shot.

    Thanks!

    Hugh


    Originally posted by Paul Young
    I think you are using SQL2K, if so how about using Application roles? I have not done this before but it might be worth checking out. Look up Application Roles in BOL.

    My theory is that you could setup an application role with dbo authority. When you need to create a superuser you would make an additional connection to the db using an application role, create the super user and then drop the connection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •