Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    19

    Unanswered: SETUSER 'dbo' in a stored procedure

    Hi,
    I want to run the following with in a stored procedure.

    SETUSER 'dbo'
    .. and do some stuff here ...
    SETUSER

    The procedure will be owned by dbo and others will have execute permissions to the procedue.

    This is not working. Dont know why?
    Any ideas?

    Thanks
    Bhargava

  2. #2
    Join Date
    Nov 2002
    Posts
    207
    Why you want to use setuser if the SP is owned by dbo?

  3. #3
    Join Date
    Nov 2003
    Posts
    19

    Unhappy

    I want to allow a batch process to drop a constaint on a table and do bcp-in, then put the constraint back. I dont want to give alter privileges on that table to BatchID. So, I wrote a stored proc in the following way:

    create proc dbo.drop_contraint_B4_bcp as
    BEGIN
    SETUSER 'dbo'
    ALTER TABLE dbo.my_table DROP CONSTRAINT my_const
    SETUSER
    END
    go
    GRANT EXECUTE ON dbo. drop_contraint_B4_bcp TO BatchID
    go

    But, when BatchID executes the above procedure, error -4 (permission error) is being returned.

    Any ideas, how this can be achieved?

  4. #4
    Join Date
    Jan 2004
    Posts
    51
    what constraints do you have?

  5. #5
    Join Date
    Nov 2003
    Posts
    19
    It is a foreign key constraint

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •