Non Database Owner call to sp_addrolemember or sp_droprolemember

[Oren]

Hi,

Is there any way that calls to sp_addrolemember and sp_droprolemember
can be enabled for non database owners and non sysadmin members?

This would be very helpful for an application I'm in the middle of
developing, in which users have the right to view some data and edit
some data in a set of tables. The data is pulled up in a set of views
(using SQL Server 7 with an Access 2000 front-end). Depending on an
initial selection that the user makes, s/he should be able to either
read or edit the data.

The solution I hoped to use would run a stored procedure, that amongst
other things would add and/or remove the user to/from a data_read and
data_edit role, depending on the initial selection s/he made.

Any suggestions?

Much thanks!
Oren Bergman

[Erland Sommarskog]

[posted and mailed, please reply in news]

Oren (or**@gdblegal.com) writes:

Is there any way that calls to sp_addrolemember and sp_droprolemember
can be enabled for non database owners and non sysadmin members?

Books Online says about permissions for sp_addrolemember:

Only members of the sysadmin fixed server role and the db_owner fixed
database role can execute sp_addrolemember to add a member to fixed
database roles. Role owners can execute sp_addrolemember to add a
member to any SQL Server role they own. Members of the db_securityadmin
fixed database role can add users to any user-defined role.

So, if the users are members of the roles that owns the role they
want to add/drop members from, they should be able to do it.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Oren]

Ernland -

Thanks for the response.
Another solution that was suggested to me, without using SQL's roles
is as follows:

- Create 2 views for the data - a read only and a r/w.
- Create a table to track the various users' permissions.
- Pull up the view corresponding to the users choices and permissions
(in the permissions table).

This way the users don't have to have extra permissions (can a role
have more than one owner in any case? If not, this would complicate
the solution you suggested). The permissions table could have certain
defaults making it easier to add new users to it.

All the best,
Oren
Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...

[posted and mailed, please reply in news]

Oren (or**@gdblegal.com) writes:

Is there any way that calls to sp_addrolemember and sp_droprolemember
can be enabled for non database owners and non sysadmin members?

Books Online says about permissions for sp_addrolemember:

Only members of the sysadmin fixed server role and the db_owner fixed
database role can execute sp_addrolemember to add a member to fixed
database roles. Role owners can execute sp_addrolemember to add a
member to any SQL Server role they own. Members of the db_securityadmin
fixed database role can add users to any user-defined role.

So, if the users are members of the roles that owns the role they
want to add/drop members from, they should be able to do it.

[Erland Sommarskog]

Oren (or**@gdblegal.com) writes:

Thanks for the response.
Another solution that was suggested to me, without using SQL's roles
is as follows:

- Create 2 views for the data - a read only and a r/w.
- Create a table to track the various users' permissions.
- Pull up the view corresponding to the users choices and permissions
(in the permissions table).

This way the users don't have to have extra permissions (can a role
have more than one owner in any case? If not, this would complicate
the solution you suggested). The permissions table could have certain
defaults making it easier to add new users to it.

A role can only have one owner, but that owner may be a role, so it would
be possible to use that solution.

However, the view solution you present appears to be more palatable. It
confines the solution to user tables/views, and requires no special
configuration. If I understood your requirements correctly, this seems
to be the best solution.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp