giving a user the ability to add logins and users to a database

I need to give a user the ability to create logins and
then add that login to a database. Is it possible to do
this without granting System Administrator Role? I cannot
seem to do this any other way then giving the user
sysadmin. Any help would be greatly apperciated.
Thanks.Add their login to the securityadmin fixed server role and to the
db_accessadmin fixed database role.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Matt." <mattcioffi@.crd.com> wrote in message
news:2e2901c428a4$9d9aec20$a601280a@.phx.gbl...
I need to give a user the ability to create logins and
then add that login to a database. Is it possible to do
this without granting System Administrator Role? I cannot
seem to do this any other way then giving the user
sysadmin. Any help would be greatly apperciated.
Thanks.|||Members of the server role securityadmin can add logins,
grant access to Windows logins.
Members of the database roles db_owner and db_accessadmin
can grant access to the databases in which they are members
of that role.
-Sue
On Thu, 22 Apr 2004 13:01:35 -0700, "Matt."
<mattcioffi@.crd.com> wrote:

>I need to give a user the ability to create logins and
>then add that login to a database. Is it possible to do
>this without granting System Administrator Role? I cannot
>seem to do this any other way then giving the user
>sysadmin. Any help would be greatly apperciated.
>Thanks.|||This does not work. I just created a user gave it
security admin role on the server and then gave it both
accessadmin and securityadmin. I get permission denied
when I try to add the user.
Please help.
>--Original Message--
>Members of the server role securityadmin can add logins,
>grant access to Windows logins.
>Members of the database roles db_owner and db_accessadmin
>can grant access to the databases in which they are
members
>of that role.
>-Sue
>On Thu, 22 Apr 2004 13:01:35 -0700, "Matt."
><mattcioffi@.crd.com> wrote:
>
cannot[vbcol=seagreen]
>.
>|||It works. I'm not sure what you missed - double check the
databases where you wanted to grant the permissions for the
new user you created - make sure you were in the correct
database. Also double check the database where the new user
is trying to add users and make sure they are executing the
procedures in the correct database.
As an example, for SomeLogin to be able to add logins and
users you would use something like:
sp_addlogin 'SomeLogin', 'SomePassword'
or
sp_grantlogin 'SomeLogin'
go
sp_addsrvrolemember 'SomeLogin', 'securityadmin'
go
use YourDatabase
go
sp_grantdbaccess 'SomeLogin'
go
sp_addrolemember 'db_accessadmin', 'SomeLogin'
go
Then when SomeLogin logs into SQL Server, they can execute:
sp_addlogin 'NewUser', 'SomeOtherPassword'
or
sp_grantlogin 'NewUser'
go
use YourDatabase
go
sp_grantdbaccess 'NewUser'
go
-Sue
On Thu, 22 Apr 2004 14:03:40 -0700,
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>This does not work. I just created a user gave it
>security admin role on the server and then gave it both
>accessadmin and securityadmin. I get permission denied
>when I try to add the user.
>Please help.
>members
>cannot|||I rechecked everything and it does not work. Are there
any server configurations or database options that need to
be set? I will delete the new user and login and try one
more time but I have tried everything and it still gets
Server: Msg 15247, Level 16, State 1, Procedure
sp_adduser, Line 35
User does not have permission to perform this action.
login has security administrator server role, the user in
the database has accessadmin, securityadmin, and at
various times other roles in the database like db_owner
and it still will not allow this to work.

>--Original Message--
>It works. I'm not sure what you missed - double check the
>databases where you wanted to grant the permissions for
the
>new user you created - make sure you were in the correct
>database. Also double check the database where the new
user
>is trying to add users and make sure they are executing
the
>procedures in the correct database.
>As an example, for SomeLogin to be able to add logins and
>users you would use something like:
>sp_addlogin 'SomeLogin', 'SomePassword'
>or
>sp_grantlogin 'SomeLogin'
>go
>sp_addsrvrolemember 'SomeLogin', 'securityadmin'
>go
>use YourDatabase
>go
>sp_grantdbaccess 'SomeLogin'
>go
>sp_addrolemember 'db_accessadmin', 'SomeLogin'
>go
>Then when SomeLogin logs into SQL Server, they can
execute:
>sp_addlogin 'NewUser', 'SomeOtherPassword'
>or
>sp_grantlogin 'NewUser'
>go
>use YourDatabase
>go
>sp_grantdbaccess 'NewUser'
>go
>-Sue
>On Thu, 22 Apr 2004 14:03:40 -0700,
><anonymous@.discussions.microsoft.com> wrote:
>
db_accessadmin[vbcol=seagreen]
do[vbcol=seagreen]
>.
>|||Sorry...don't know what else to suggest. There are no
special server or database configurations, options needed to
allow a member of db_accessadmin to execute
sp_grantdbaccess.
-Sue
On Mon, 26 Apr 2004 06:09:57 -0700, "Matt"
<mattcioffi@.crd.com> wrote:
[vbcol=seagreen]
>I rechecked everything and it does not work. Are there
>any server configurations or database options that need to
>be set? I will delete the new user and login and try one
>more time but I have tried everything and it still gets
>Server: Msg 15247, Level 16, State 1, Procedure
>sp_adduser, Line 35
>User does not have permission to perform this action.
>login has security administrator server role, the user in
>the database has accessadmin, securityadmin, and at
>various times other roles in the database like db_owner
>and it still will not allow this to work.
>
>
>the
>user
>the
>execute:
>db_accessadmin
>do