adding new users

SQL 2005 Express;
Win XP/2003
Our app defines an Admin for the application.
He has a database login.
An Admin can add users, which then get created on
the server and added to the database.
This appears to require that he have sysadmin.
We don't want them to be able to log directly into
the database with sysadmin priviledges.
We thought about using Application roles, but we
are reluctant to hardcode the password in the
application, thus having the same password across
installations.
Any other ideas on how to let then create users on the
server without the Admin having sysadmin?
--
Thanks,
Brad.Look at the fixed server and database roles. See 'Roles' in Books Online.
For example:
The securityadmin role can add Logins -but cannot provide any access or perm
issions. That is left up to the appropriate database role.
Could that serve your purpose?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad White" <bwhite at inebraska . com> wrote in message news:%23CL1EQq$GHA.4328@.TK2MSFTNGP
03.phx.gbl...
> SQL 2005 Express;
> Win XP/2003
>
> Our app defines an Admin for the application.
> He has a database login.
>
> An Admin can add users, which then get created on
> the server and added to the database.
> This appears to require that he have sysadmin.
>
> We don't want them to be able to log directly into
> the database with sysadmin priviledges.
>
> We thought about using Application roles, but we
> are reluctant to hardcode the password in the
> application, thus having the same password across
> installations.
>
> Any other ideas on how to let then create users on the
> server without the Admin having sysadmin?
> --
> Thanks,
> Brad.
>
>|||Look at the fixed server and database roles. See 'Roles' in Books Online.
For example:
The securityadmin role can add Logins -but cannot provide any access or perm
issions. That is left up to the appropriate database role.
Could that serve your purpose?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad White" <bwhite at inebraska . com> wrote in message news:%23CL1EQq$GHA.4328@.TK2MSFTNGP
03.phx.gbl...
> SQL 2005 Express;
> Win XP/2003
>
> Our app defines an Admin for the application.
> He has a database login.
>
> An Admin can add users, which then get created on
> the server and added to the database.
> This appears to require that he have sysadmin.
>
> We don't want them to be able to log directly into
> the database with sysadmin priviledges.
>
> We thought about using Application roles, but we
> are reluctant to hardcode the password in the
> application, thus having the same password across
> installations.
>
> Any other ideas on how to let then create users on the
> server without the Admin having sysadmin?
> --
> Thanks,
> Brad.
>
>|||Looks promising. I'll check it out.
Thanks,
Brad.
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23i5oJYr$GHA.4740@.TK2MSFTNGP03.phx.gbl...
> Look at the fixed server and database roles. See 'Roles' in Books Online.
> For example:
> The securityadmin role can add Logins -but cannot provide any access or
> permissions. That is left up to the
> appropriate database role.
> Could that serve your purpose?
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
"Brad White" <bwhite at inebraska . com> wrote in message
news:%23CL1EQq$GHA.4328@.TK2MSFTNGP03.phx.gbl...
> SQL 2005 Express;
> Win XP/2003
> Our app defines an Admin for the application.
> He has a database login.
> An Admin can add users, which then get created on
> the server and added to the database.
> This appears to require that he have sysadmin.
> We don't want them to be able to log directly into
> the database with sysadmin priviledges.
> We thought about using Application roles, but we
> are reluctant to hardcode the password in the
> application, thus having the same password across
> installations.
> Any other ideas on how to let then create users on the
> server without the Admin having sysadmin?
> --
> Thanks,
> Brad.
>|||In initial tests, it looks like that does the trick.
--
Thanks,
Brad.
"Arnie Rowland" <arnie@.1568.com> wrote in message news:%23i5oJYr$GHA.4740@.TK
2MSFTNGP03.phx.gbl...
Look at the fixed server and database roles. See 'Roles' in Books Online.
For example:
The securityadmin role can add Logins -but cannot provide any access or perm
issions. That is left up to the appropriate database role.
Could that serve your purpose?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad White" <bwhite at inebraska . com> wrote in message news:%23CL1EQq$GHA.4328@.TK2MSFTNGP
03.phx.gbl...
> SQL 2005 Express;
> Win XP/2003
>
> Our app defines an Admin for the application.
> He has a database login.
>
> An Admin can add users, which then get created on
> the server and added to the database.
> This appears to require that he have sysadmin.
>
> We don't want them to be able to log directly into
> the database with sysadmin priviledges.
>
> We thought about using Application roles, but we
> are reluctant to hardcode the password in the
> application, thus having the same password across
> installations.
>
> Any other ideas on how to let then create users on the
> server without the Admin having sysadmin?
> --
> Thanks,
> Brad.
>
>|||We have a database user that has SecurityAdmin on the
server and on the database.
It can add users to the Server, but
a) can't run sp_adduser: not enough permissions to run SPs.
b) can't add the user to the DB directly:
Create user 'username' for login 'loginname'
No permissions to add user to the DB.
Tried giving this user permission to exec the SP, and
that failed as well.
Making this user an owner of the DB, obviously solves
the problem, but we'd really rather not. 8:-)
What other minimum server roles or db roles can we add
to get this?
--
Thanks,
Brad.
"Brad White" <bwhite at inebraska . com> wrote in message news:OlwDcbt$GHA.4
992@.TK2MSFTNGP03.phx.gbl...
In initial tests, it looks like that does the trick.
--
Thanks,
Brad.
"Arnie Rowland" <arnie@.1568.com> wrote in message news:%23i5oJYr$GHA.4740@.TK
2MSFTNGP03.phx.gbl...
Look at the fixed server and database roles. See 'Roles' in Books Online.
For example:
The securityadmin role can add Logins -but cannot provide any access or perm
issions. That is left up to the appropriate database role.
Could that serve your purpose?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad White" <bwhite at inebraska . com> wrote in message news:%23CL1EQq$GHA.4328@.TK2MSFTNGP
03.phx.gbl...
> SQL 2005 Express;
> Win XP/2003
>
> Our app defines an Admin for the application.
> He has a database login.
>
> An Admin can add users, which then get created on
> the server and added to the database.
> This appears to require that he have sysadmin.
>
> We don't want them to be able to log directly into
> the database with sysadmin priviledges.
>
> We thought about using Application roles, but we
> are reluctant to hardcode the password in the
> application, thus having the same password across
> installations.
>
> Any other ideas on how to let then create users on the
> server without the Admin having sysadmin?
> --
> Thanks,
> Brad.
>
>|||As I indicated earlier, the securityadmin role can add logins -but cannot gi
ve permissions to the databases, or to the objects in the databases. I sugge
sted that you check in Books Online about the use of the fixed server roles
and the fixed database roles.
It sounds like you need both a server fixed role AND a database fixed role.
When you finally check in Books Online, check out the capabilities of the db
_accessadmin and the db_securityadmin database roles.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad White" <bwhite at inebraska . com> wrote in message news:%237KjVe2$GHA
.1196@.TK2MSFTNGP02.phx.gbl...
We have a database user that has SecurityAdmin on the
server and on the database.
It can add users to the Server, but
a) can't run sp_adduser: not enough permissions to run SPs.
b) can't add the user to the DB directly:
Create user 'username' for login 'loginname'
No permissions to add user to the DB.
Tried giving this user permission to exec the SP, and
that failed as well.
Making this user an owner of the DB, obviously solves
the problem, but we'd really rather not. 8:-)
What other minimum server roles or db roles can we add
to get this?
--
Thanks,
Brad.
"Brad White" <bwhite at inebraska . com> wrote in message news:OlwDcbt$GHA.4
992@.TK2MSFTNGP03.phx.gbl...
In initial tests, it looks like that does the trick.
--
Thanks,
Brad.
"Arnie Rowland" <arnie@.1568.com> wrote in message news:%23i5oJYr$GHA.4740@.TK
2MSFTNGP03.phx.gbl...
Look at the fixed server and database roles. See 'Roles' in Books Online.
For example:
The securityadmin role can add Logins -but cannot provide any access or perm
issions. That is left up to the appropriate database role.
Could that serve your purpose?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad White" <bwhite at inebraska . com> wrote in message news:%23CL1EQq$GHA.4328@.TK2MSFTNGP
03.phx.gbl...
> SQL 2005 Express;
> Win XP/2003
>
> Our app defines an Admin for the application.
> He has a database login.
>
> An Admin can add users, which then get created on
> the server and added to the database.
> This appears to require that he have sysadmin.
>
> We don't want them to be able to log directly into
> the database with sysadmin priviledges.
>
> We thought about using Application roles, but we
> are reluctant to hardcode the password in the
> application, thus having the same password across
> installations.
>
> Any other ideas on how to let then create users on the
> server without the Admin having sysadmin?
> --
> Thanks,
> Brad.
>
>