locked
Creating Custom Stored Procedure RRS feed

  • Question

  • i am trying to create a stored procedure that would accomplish a few different things. 

    the stored procedure will be an automated job that runs aevery 5 minutes or so. The main purpose of the SP is for user management.

    So for instance I want to grant user a access to a database in 2 days and user a needs read and write access to the db. so user a gets read/write access to the db in 2 days and in 3 days they loose that access. So the user only has access to the database for a total of 24 hrs.

    The SP needs to be able to do these things listed:

    Search a table to determine if any users need access granted or revoked based on the time and date specified in the table.

    Add users to db roles and remove them upon completion of their work.

    Friday, March 6, 2015 7:24 PM

Answers

  • Hi Henry Offiah,

    It sound to me like you do not need a regular stored procedure that execute as JOB in schedule time. There is no reason to execute this SP for example every 10 min if the user only logon the server once in a day. Moreover, if you executed this SP at 23:10 and the user logon on 23:30, and the SP execute every 1 hour, then when does his 24 hours start?

    You probably need a simple LOGON trigger.

    You can create a LOGON trigger that check information from a table and update the table as well. It can give the user new privileges before he start to work (on LOGON event).


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    Saturday, March 7, 2015 2:46 AM
  • ... or if the data is coming from another system or application, have it execute a SP that puts the requests into a service broker queue and use an actives stored procedure. Then it's real time.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Saturday, March 7, 2015 2:52 AM
    Answerer

All replies

  • Hi Henry Offiah,

    It sound to me like you do not need a regular stored procedure that execute as JOB in schedule time. There is no reason to execute this SP for example every 10 min if the user only logon the server once in a day. Moreover, if you executed this SP at 23:10 and the user logon on 23:30, and the SP execute every 1 hour, then when does his 24 hours start?

    You probably need a simple LOGON trigger.

    You can create a LOGON trigger that check information from a table and update the table as well. It can give the user new privileges before he start to work (on LOGON event).


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    Saturday, March 7, 2015 2:46 AM
  • ... or if the data is coming from another system or application, have it execute a SP that puts the requests into a service broker queue and use an actives stored procedure. Then it's real time.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Saturday, March 7, 2015 2:52 AM
    Answerer