Minimum Permissions for sp_adduser

Hi,
I created a new user and made it a member of 3 roles. db_owner,
db_securityadmin and db_accessadmin.

But still i am not able to execute sp_adduser using this new user.

I am getting the error
"User XXX does not have permission to run DBCC auditevents"

Can u please help me with this.

"Russell Fields" wrote:

> Munish
>
> From the BOL: "Only the dbo and members of the sysadmin fixed server role
> can execute sp_adduser." (I assume that dbo includes members of the
> db_owner role, but I have not tested this today.)
>
> So, within a database a dbo can add users, but granting execute on the
> stored procedure will not add the rights to a non-dbo. (And, of course,
> adding logins is higher rights.)
>
> RLF
>
> "Munish Narula" wrote in message
>
> >I am trying to create a new user who has the permission to create a new
> >user
> > in SQL 2000. I have greated him only the rights for excuting sp_adduser.
> > But
> > still this user is not able to create any other user. Is this not suffient
> > privilege?
> >
> > What are the minimum set of privilleges required for a user to execute
> > sp_adduser. I am talking only about SQL 2000 here.
>
>
 >> Stay informed about: Minimum Permissions for sp_adduser 

Erland already answered this. Use CREATE USER and grant ALTER ANY USER to
the user.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Munish Narula" wrote in message

Hi,
I created a new user and made it a member of 3 roles. db_owner,
db_securityadmin and db_accessadmin.

But still i am not able to execute sp_adduser using this new user.

I am getting the error
"User XXX does not have permission to run DBCC auditevents"

Can u please help me with this.

"Russell Fields" wrote:

> Munish
>
> From the BOL: "Only the dbo and members of the sysadmin fixed server role
> can execute sp_adduser." (I assume that dbo includes members of the
> db_owner role, but I have not tested this today.)
>
> So, within a database a dbo can add users, but granting execute on the
> stored procedure will not add the rights to a non-dbo. (And, of course,
> adding logins is higher rights.)
>
> RLF
>
> "Munish Narula" wrote in message
>
> >I am trying to create a new user who has the permission to create a new
> >user
> > in SQL 2000. I have greated him only the rights for excuting sp_adduser.
> > But
> > still this user is not able to create any other user. Is this not
> > suffient
> > privilege?
> >
> > What are the minimum set of privilleges required for a user to execute
> > sp_adduser. I am talking only about SQL 2000 here.
>
>
 >> Stay informed about: Minimum Permissions for sp_adduser 

Munish,

It turns out that db_owner will not do, the account must actually be the
owner of the database and therefore the 'dbo' user. However, according to
the SQL Server 2000 Books Online:

db_accessadmin - Can add or remove user IDs.
db_securityadmin - Can manage all permissions, object ownerships, roles and
role memberships.

So, db_accessadmin seems to be what you need.

However, "sp_adduser" is hardcoded to reject even these rights. But if you
change to the SQL Server 2000 preferred command, "sp_grantdbaccess" it will
work for you.

Regarding your latest error message, it is discussed (if you are curious) in
this article by Brian Kelley.
http://www.sqlservercentral.com/articles/Security/sqlserversecuritypro...dconsof

Please note that in SQL Server 2005 both of these stored procedures have
been superceded by:
CREATE USER

RLF






"Munish Narula" wrote in message

> Hi,
> I created a new user and made it a member of 3 roles. db_owner,
> db_securityadmin and db_accessadmin.
>
> But still i am not able to execute sp_adduser using this new user.
>
> I am getting the error
> "User XXX does not have permission to run DBCC auditevents"
>
> Can u please help me with this.
>
> "Russell Fields" wrote:
>
>> Munish
>>
>> From the BOL: "Only the dbo and members of the sysadmin fixed server role
>> can execute sp_adduser." (I assume that dbo includes members of the
>> db_owner role, but I have not tested this today.)
>>
>> So, within a database a dbo can add users, but granting execute on the
>> stored procedure will not add the rights to a non-dbo. (And, of course,
>> adding logins is higher rights.)
>>
>> RLF
>>
>> "Munish Narula" wrote in message
>>
>> >I am trying to create a new user who has the permission to create a new
>> >user
>> > in SQL 2000. I have greated him only the rights for excuting
>> > sp_adduser.
>> > But
>> > still this user is not able to create any other user. Is this not
>> > suffient
>> > privilege?
>> >
>> > What are the minimum set of privilleges required for a user to execute
>> > sp_adduser. I am talking only about SQL 2000 here.
>>
>>
 >> Stay informed about: Minimum Permissions for sp_adduser 

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?

"Tom Moreau" wrote in message

> Erland already answered this. Use CREATE USER and grant ALTER ANY USER to
> the user.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>
> "Munish Narula" wrote in message
>
> Hi,
> I created a new user and made it a member of 3 roles. db_owner,
> db_securityadmin and db_accessadmin.
>
> But still i am not able to execute sp_adduser using this new user.
>
> I am getting the error
> "User XXX does not have permission to run DBCC auditevents"
>
> Can u please help me with this.
>
> "Russell Fields" wrote:
>
>> Munish
>>
>> From the BOL: "Only the dbo and members of the sysadmin fixed server role
>> can execute sp_adduser." (I assume that dbo includes members of the
>> db_owner role, but I have not tested this today.)
>>
>> So, within a database a dbo can add users, but granting execute on the
>> stored procedure will not add the rights to a non-dbo. (And, of course,
>> adding logins is higher rights.)
>>
>> RLF
>>
>> "Munish Narula" wrote in message
>>
>> >I am trying to create a new user who has the permission to create a new
>> >user
>> > in SQL 2000. I have greated him only the rights for excuting
>> > sp_adduser.
>> > But
>> > still this user is not able to create any other user. Is this not
>> > suffient
>> > privilege?
>> >
>> > What are the minimum set of privilleges required for a user to execute
>> > sp_adduser. I am talking only about SQL 2000 here.
>>
>>
>
 >> Stay informed about: Minimum Permissions for sp_adduser 

Uri Dimant (urid@iscar.co.il) 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.TakeThisOut@sommarskog.se

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
 >> Stay informed about: Minimum Permissions for sp_adduser 

No, he isn't. Also the OP said that the user was already member of db_owner
database role

"Erland Sommarskog" wrote in message

> Uri Dimant (urid@iscar.co.il) 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 DeleteThis @sommarskog.se
>
> 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
>
 >> Stay informed about: 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" wrote in message

> No, he isn't. Also the OP said that the user was already member of
> db_owner database role
>
> "Erland Sommarskog" wrote in message
>
>> Uri Dimant (urid@iscar.co.il) 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.DeleteThis@sommarskog.se
>>
>> 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
>>
>
>
 >> Stay informed about: Minimum Permissions for sp_adduser