How To Switch Standard Roles

Question

How To Switch Standard Roles

Dan Harrison-283631

Newbie

Points: 2
More actions
December 6, 2005 at 10:39 am

#90084

I have a database that has several standard roles (not application roles) defined. The roles provide a strong level of security for our users and each user is assigned to a role. Certain Administrative users are assigned to multiple roles so they can login to our application and see the each roles restrictions.
We have a few stored procedures that execute dynamic SQL and dynamically reference a view. I would llike our Administrative users to have the capability of actually switching roles so that they are in the securty role to ensure the results of any access are accurate (reflect a specific role). Is there any command that can be entered via SQL or placed in a stored procedure to switch to a different role?

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

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

ramses2nd Hall of Fame Points: 3720 More actions · Answer 1

Off the top of my head you could use the sp_droprolemember and sp_addrolemember. The only problem is that you must be a member of the sysadmin fixed server role, the db_owner or db_securityadmin fixed database role to execute these.

EXEC sp_droprolemember 'Rolename', 'username'

EXEC sp_addrolemember 'Rolename', 'username'

Dan Harrison-283631 Newbie Points: 2 More actions · Answer 2

Thanks ramses2nd;

The add and drop role will work but I need to switch the roles fairly often and hope there is another technique.

I had used a technique in antoher database (Sybase) that allowed the group id to be updated in a stored procedure, but it seems that the group id in SQL Server is generated and an update to this field is not allowed.

Thansk for the response.

Dan

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

Create a dummy SQL user in each role, and then the admin users can use SETUSER to impersonate a user in any role.