How to allow user to sp_adduser?

[WDfog]

I have a database that I'd like to have a user administer for new users. However, I cannot set this one user up to be able to run the sp_adduser procedure without setting him up as a sys_admin or a dbo of that database.

I thought of adding him to the dbo group and then denying many rights, but the all critical DROP command cannot be revoked.

Any suggestions?

Thanks,
Wendy [sig][/sig]

 

[foxdev]

From the BOL:

Only dbo can execute sp_adduser.

If it helps, you might try sp_grantdbaccess:

Only members of the db_accessadmin or db_owner fixed database roles can execute sp_grantdbaccess.
[sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[karluk]

I suggest that you investigate using sp_grantdbaccess instead of sp_adduser. The documentation says that sp_adduser requires dbo authority, but sp_grantdbaccess only requires the db_accessadmin role. [sig][/sig]

 

[WDfog]

Thanks for the help, but unfortunately, I am stuck with the same error message of 'only members of the sysadmin role or the database owner can execute this stored procedure' even after putting the user into the db_accessadmin role and using sp_grantdbaccess. [sig][/sig]

 

[karluk]

Could you describe your problem in more detail? I tried a quick test and was able to use sp_grantdbaccess with just db_accessadmin, not dbo. Here's what I did.

1. Logged on as sa and created user test1 with db_accessadmin in database db01, but no other permissions.

2. As sa created user test2 with no rights in db01.

3. Logged in to query analyzer as test2 and tried to execute the command &quot;use db01&quot;. This failed because test2 has no rights in db01.

4. Logged in to query analyzer as test1 and executed the command &quot;sp_grantdbaccess test2&quot;.

5. Logged in to query analyzer as test2. This time the command &quot;use db01&quot; succeeded.

This is admittedly a superficial test, but it does appear that it's not necessary to have db owner authority to execute sp_grantdbaccess. [sig][/sig]