Creating users from stored proc

[Andrew Merisanu]

Hello All,

I need to create logins and users in SQL Server 7 from a stored proc (or
trigger) whenever an employee is added to the Employees table in the
database.

I also need to grant the user that is created rights to all views and stored
procs in the database.

The problem is that this is all done from a user connection. i.e. the
manager that is adding the employee is logged in not as SA, but as a regular
user.

If anyone has any ideas how this whole thing can be accomplished, I would
really appreciate the help.

TIA

Andrew Merisanu

[DaveSatz]

If you are not going to assign the manager to a security role in SQL Server,
then you cannot use a real-time TSQL solution. Why not create an insert
trigger on the employee table that capture new employee id's. Then have a
scheduled task running as sa that reads the table, creates the
logins/users/permissions and deletes the row created in the trigger.
Something like that...

There are numerous non-SQL possiblities depending on the technology you
want to use.
--
HTH,
David Satz
Principal Software Engineer
Hyperion Solutions
->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------

"Andrew Merisanu" <andrewm@couriercomplete.com> wrote in message
news:3aa27632@news.devx.com...
> Hello All,
>
> I need to create logins and users in SQL Server 7 from a stored proc (or
> trigger) whenever an employee is added to the Employees table in the
> database.
>
> I also need to grant the user that is created rights to all views and
stored
> procs in the database.
>
> The problem is that this is all done from a user connection. i.e. the
> manager that is adding the employee is logged in not as SA, but as a
regular
> user.
>
> If anyone has any ideas how this whole thing can be accomplished, I would
> really appreciate the help.
>
> TIA
>
> Andrew Merisanu
>
>

[Simon Sellick]

Hi,

I might have imagined this (I'm not using SQL Server at present, but I did
last year for a while), but doesn't it offer a facility within a SP to execute
either as user or as owner? In that case, the SP just needs to be owned
by SA. Otherwise, ignore the meanderings of a crazed mind...

Simon.

"DaveSatz" <davidsatz@yahoo.com> wrote:
>If you are not going to assign the manager to a security role in SQL Server,
>then you cannot use a real-time TSQL solution. Why not create an insert
>trigger on the employee table that capture new employee id's. Then have
a
>scheduled task running as sa that reads the table, creates the
>logins/users/permissions and deletes the row created in the trigger.
>Something like that...
>
> There are numerous non-SQL possiblities depending on the technology you
>want to use.
>--
>HTH,
>David Satz
>Principal Software Engineer
>Hyperion Solutions
>->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
>(Please reply to group only - emails answered rarely)
>-----------------------------------------------------------------
>
>"Andrew Merisanu" <andrewm@couriercomplete.com> wrote in message
>news:3aa27632@news.devx.com...
>> Hello All,
>>
>> I need to create logins and users in SQL Server 7 from a stored proc (or
>> trigger) whenever an employee is added to the Employees table in the
>> database.
>>
>> I also need to grant the user that is created rights to all views and
>stored
>> procs in the database.
>>
>> The problem is that this is all done from a user connection. i.e. the
>> manager that is adding the employee is logged in not as SA, but as a
>regular
>> user.
>>
>> If anyone has any ideas how this whole thing can be accomplished, I would
>> really appreciate the help.
>>
>> TIA
>>
>> Andrew Merisanu
>>
>>
>
>

[DaveSatz]

SETUSER (T-SQL) will do something like this, but it only allows a member of
the sysadmin fixed server role or db_owner fixed database role to
impersonate another user.

"Simon Sellick" <simon.sellick@tesco.net> wrote in message
news:3aa3c790$1@news.devx.com...
>
> Hi,
>
> I might have imagined this (I'm not using SQL Server at present, but I did
> last year for a while), but doesn't it offer a facility within a SP to
execute
> either as user or as owner? In that case, the SP just needs to be owned
> by SA. Otherwise, ignore the meanderings of a crazed mind...
>
> Simon.
>
> "DaveSatz" <davidsatz@yahoo.com> wrote:
> >If you are not going to assign the manager to a security role in SQL
Server,
> >then you cannot use a real-time TSQL solution. Why not create an insert
> >trigger on the employee table that capture new employee id's. Then have
> a
> >scheduled task running as sa that reads the table, creates the
> >logins/users/permissions and deletes the row created in the trigger.
> >Something like that...
> >
> > There are numerous non-SQL possiblities depending on the technology you
> >want to use.
> >--
> >HTH,
> >David Satz
> >Principal Software Engineer
> >Hyperion Solutions
> >->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
> >(Please reply to group only - emails answered rarely)
> >-----------------------------------------------------------------
> >
> >"Andrew Merisanu" <andrewm@couriercomplete.com> wrote in message
> >news:3aa27632@news.devx.com...
> >> Hello All,
> >>
> >> I need to create logins and users in SQL Server 7 from a stored proc
(or
> >> trigger) whenever an employee is added to the Employees table in the
> >> database.
> >>
> >> I also need to grant the user that is created rights to all views and
> >stored
> >> procs in the database.
> >>
> >> The problem is that this is all done from a user connection. i.e. the
> >> manager that is adding the employee is logged in not as SA, but as a
> >regular
> >> user.
> >>
> >> If anyone has any ideas how this whole thing can be accomplished, I
would
> >> really appreciate the help.
> >>
> >> TIA
> >>
> >> Andrew Merisanu
> >>
> >>
> >
> >
>