Grant Create User permission

Question

Grant Create User permission

mobasha

SSCertifiable

Points: 5334
More actions
May 12, 2008 at 2:46 am

#207265

:cool:i have database1, i have been trying to give user1 the right permission to manage (add)only the users in this database, but am getting an error msg.
this is what i worte:
use database1
go
GRANT EXECUTE ON [sys].sp_adduser TO [user1];
error:Msg 4629, Level 16, State 10, Line 1
Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master.
and when i execute this:
use master
go
GRANT EXECUTE ON [sys].sp_adduser TO [user1];
then the user have it permission on the master, anyway when i tried to create a user its not working:
Msg 15247, Level 16, State 1, Procedure sp_adduser, Line 35
User does not have permission to perform this action.
so what permission should the user have in order to create users in the database??
..>>..
MobashA

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

GRANT ALTER ANY USER ON Database1 TO User1

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mobasha SSCertifiable Points: 5334 More actions · Answer 2

ok it did worked.

can i give a user the permission to do every thing but not drop any object from the dbo schema?

..>>..

MobashA

codewatcher11 Mr or Mrs. 500 Points: 519 More actions · Answer 3

GilaMonster (5/12/2008)
GRANT ALTER ANY USER ON Database1 TO User1

hi GilaMonster!

i didn't get your suggestion,

can you please elaborate? or set the format of the syntax? thanks!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

What didn't you understand? What I posted is the syntax (as an example) of a grant statement that grants the permission to alter any user in the database Database1 to the user User1

If you don't understand, may I suggest a look at Books Online, specifically the section on the Grant statement and SQL security.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

codewatcher11 Mr or Mrs. 500 Points: 519 More actions · Answer 5

GilaMonster (11/20/2008)
What didn't you understand? What I posted is the syntax (as an example) of a grant statement that grants the permission to alter any user in the database Database1 to the user User1
If you don't understand, may I suggest a look at Books Online, specifically the section on the Grant statement and SQL security.

i did not understand because i has says, "incorrect syntax.." and im sorry that i have books online,but it doesn't behave well. just take a look at this screenshot.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

The login name is not a valid identifier. Put square brackets around it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass