Hello Group

Ok, I've read the BOL topic on sp_adduser and have done some research here
in the groups but the 'why' question remains...

A simple thing:
I would like to empower 'User A' to be able to add logins and users to
database 'ABC' so I don't have to do it.
Let's assume I'm far away on a tropical island with no remote access for
several months ...ahhhh

Now, I've added the Login for 'User A' to the server roles 'Security
Administrators' and 'System Administrators' and given access to the
databases 'ABC' and 'master' - it all works, 'User A' can create logins and
users for 'ABC'

What I find a little bit disturbing is, that this gives 'User A' the
possibility to do anything with any database and I just wondered whether
there's a way.. to avoid this perhaps?

So just createing logins and creating users permissions for database 'ABC'
and as little as needed from everything else...
Please describe in detail on how to do this as I'm new to this and don't
have much of a clue..as you see!

Later on, I might want to create a 'UserB' with the same capabilities but
for now I'm just happy to get going.

Thanks for your help & efforts!!

Martin

Ok.. sp_grantdbaccess seems to do the trick.. also all samples are for NT
Users, it seems to work except for a message that the user already exists
when it is indeed not. But still creates it. I can trap this error but
wonder whether this is the right way??

That's my code:

EXEC @ReturnCode = sp_addlogin @ProgUserLogin, @ProgUserPassword,
'invent2b_com'
EXEC @ReturnCode = sp_grantdbaccess @ProgUserLogin, @ProgUserName
EXEC @ReturnCode = sp_addrolemember @SQLRole, @ProgUserLogin

Thank you very much for your thoughts!

Martin


"Martin Feuersteiner" <theintrepidfox@hotmail.com> wrote in message
news:ceb6fc$2jk$1@hercules.btinternet.com...
> Hello Group
>
> Ok, I've read the BOL topic on sp_adduser and have done some research here
> in the groups but the 'why' question remains...
>
> A simple thing:
> I would like to empower 'User A' to be able to add logins and users to
> database 'ABC' so I don't have to do it.
> Let's assume I'm far away on a tropical island with no remote access for
> several months ...ahhhh
>
> Now, I've added the Login for 'User A' to the server roles 'Security
> Administrators' and 'System Administrators' and given access to the
> databases 'ABC' and 'master' - it all works, 'User A' can create logins
and
> users for 'ABC'
>
> What I find a little bit disturbing is, that this gives 'User A' the
> possibility to do anything with any database and I just wondered whether
> there's a way.. to avoid this perhaps?
>
> So just createing logins and creating users permissions for database 'ABC'
> and as little as needed from everything else...
> Please describe in detail on how to do this as I'm new to this and don't
> have much of a clue..as you see!
>
> Later on, I might want to create a 'UserB' with the same capabilities but
> for now I'm just happy to get going.
>
> Thanks for your help & efforts!!
>
> Martin
>
>

"Martin Feuersteiner" <theintrepidfox@hotmail.com> wrote in message
news:ceb6fc$2jk$1@hercules.btinternet.com...
> Hello Group
>
> Ok, I've read the BOL topic on sp_adduser and have done some research here
> in the groups but the 'why' question remains...
>
> A simple thing:
> I would like to empower 'User A' to be able to add logins and users to
> database 'ABC' so I don't have to do it.
> Let's assume I'm far away on a tropical island with no remote access for
> several months ...ahhhh
>
> Now, I've added the Login for 'User A' to the server roles 'Security
> Administrators' and 'System Administrators' and given access to the
> databases 'ABC' and 'master' - it all works, 'User A' can create logins
and
> users for 'ABC'
>
> What I find a little bit disturbing is, that this gives 'User A' the
> possibility to do anything with any database and I just wondered whether
> there's a way.. to avoid this perhaps?
>
> So just createing logins and creating users permissions for database 'ABC'
> and as little as needed from everything else...
> Please describe in detail on how to do this as I'm new to this and don't
> have much of a clue..as you see!
>
> Later on, I might want to create a 'UserB' with the same capabilities but
> for now I'm just happy to get going.
>
> Thanks for your help & efforts!!
>
> Martin
>
>

I believe that adding UserA to securityadmin (manage logins) and
db_accessadmin in ABC (manage users in that database only) should do what
you want. UserA can create as many logins as he wants, but they're not much
use without access to databases. And being in db_accessadmin does not give
you the right to create objects or view data in that database. But I admit
that I haven't tested this, and there are some potential issues - only
sysadmin/db_owner can use sp_change_users_login to fix orphaned users, for
example.

Simon