Creating users from stored proc


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Creating users from stored proc

  1. #1
    Andrew Merisanu Guest

    Creating users from stored proc

    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



  2. #2
    DaveSatz Guest

    Re: Creating users from stored proc

    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
    >
    >




  3. #3
    Simon Sellick Guest

    Re: Creating users from stored proc


    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
    >>
    >>

    >
    >



  4. #4
    DaveSatz Guest

    Re: Creating users from stored proc

    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
    > >>
    > >>

    > >
    > >

    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center