0

using a stored procedure, how can i restrict what a user INSERTS using a insert query, which could be restricted using WITH CHECK OPTION of a View?

like i want only steve and peter to insert data into a ITEM table. i have added a column 'USERS' in the ITEM table which consists of all the usernames. for this if i use view , then in the check option i give : 

WHERE username='steve' OR username='peter' WITH CHECK OPTION

but how do i do this using a stored procedure?

sqlchild
  • 8,754
  • 28
  • 105
  • 167

1 Answers1

3

Something like this. You have to test for a user first and decide whether to allow. The test can be wrapped into another stored proc or UDF for re-use.

Question: how does your view know that Steve or Peter are the ones executing the view?

CREATE PROC myProc
   @p1 int
AS
SET NOCOUNT ON

BEGIN TRY
    IF SUSER_SNAME() NOT IN ('steve', 'peter')
       RAISERROR ('Oi: Steve and Peter only', 16, 1)

END TRY
BEGIN CATCH
   ...
END CATCH
GO

See my answer here for a stored proc template

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • steve would login into the application. so when he would execute the view then the view would check his user name. as my table already has the usernames inserted into it previously – sqlchild Mar 14 '11 at 07:48