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 ScottI 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.|||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.sql