SQL Server - sp_adduser

Asked By AdamSanke

24-Jan-08 11:17 AM

Hi,
I have 3 instances of SQL2K running in a clustered environment. If I log in
to a database as a member of the group db_owner and thenI run sp_adduser, it
fail's saying I do not have sufficient permissions to run that procedure.
However if I run sp_grantdbaccess it works fine. Why is this?

If I change the user to be the dbo (sp_changedbowner) then sp_adduser works
fine. Can anybody explain this to me please?

Thanks
Adam

24-Jan-08 01:10 PM

Seems to be by design. my Cut&Paste doesn't work from the VM where I run SQL2K. But check BOL for
both sp_adduser and sp_grantdbaccess. See Permissions section for each and you will see that this is
by design.


--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

24-Jan-08 01:20 PM

Hi Adam,

According to BOL sp_adduser is included for backward compatibility only, so
you should use sp_grantdbaccess instead.

Regarding the permissions every entry on BOL has a Permissions section
almost at the end and in this case the permissions are a little bit different
for these two T-SQL statements.

Hope this helps,

Ben Nevarez

24-Jan-08 04:19 PM

Adam;

If you run sp_helptext 'sp_adduser' and sp_helptext 'sp_grantdbaccess', and
compare how each does its permission checks, you'll find that sp_adduser
checks for is_member('dbo'), whereas sp_grantdbaccess checks for
is_member('db_owner').

A user that is not dbo, but a member of db_owner fixed role, will fail
is_member('dbo') check, thus the situation you ran into.

Linchi