Re: Minimum Permissions for sp_adduser

Uri,

Yes he did say he was db_owner, per my first suggestion.

But it turns out that being a member of db_owner is not enough, even though it should be, because it does not map a login to the "dbo" user. (That mapping is reserved for the owner of the database (the SID in sysdatabases) and for 'sa' or other sysadmins.)

The sp_adduser procedure is hardcoded, not for the rights you actually have, but for whether or not you are the "dbo" user.

RLF


"Uri Dimant" <urid@xxxxxxxxxxx> wrote in message news:uegGc1LaJHA.4852@xxxxxxxxxxxxxxxxxxxxxxx
No, he isn't. Also the OP said that the user was already member of db_owner database role

"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message news:Xns9B8077C38C6E8Yazorman@xxxxxxxxxxxx
Uri Dimant (urid@xxxxxxxxxxx) writes:
Hi Tom
But the OP is using SQL Server 2000
I suggested to him adding the user into securityadmin server role and try
it again, what do you think?

Oops! But didn't he say SQL 2005 in his first post?

Anyway, I think Russell has already sorted this out with help of
sp_helptext: sp_adduser requires that you are dbo, period. But the
newer sp_grantdbaccess, being a member of db_accessadmin is sufficient.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx




.

Relevant Pages

  • Re: System Administrator Implied Permissions
    ... member of the dbo group and you are automatically given ... Now, given this, why does SQL Server ... Much Thanks Dan. ...
    (microsoft.public.sqlserver.security)
  • Re: Table ownership
    ... Perhaps the user isn't dbo, but is only a member of db_owner? ... specified the owner when the object is created: ... > reason on one SQL Server this is not the case. ...
    (microsoft.public.sqlserver.server)
  • Re: DBO Best Practices
    ... lines of always owner qualifying objects... ... someone who are just "a" dbo. ... > doesn't explicitly reference the object's owner (because SQL Server will ... The startup account defines the ...
    (microsoft.public.sqlserver.security)
  • Re: Keeping User From Deleting Table Records
    ... Hugo - Your comment about the user being a member of an admin group did the ... - I have created a new Windows user login in Security/Logins that is ... SQL Server to obtain default settings for the additional configuration ...
    (microsoft.public.sqlserver.security)
  • Re: DBO Best Practices
    ... If you let DBO own databases or objects then regular ... Please note that the DBO user is identified by the SQL Server process ... the SQL Server startup account (for the ... SQL Server process) controls SQL Server's ability to work with NT's objects ...
    (microsoft.public.sqlserver.security)