locked
How to use dbo procedure to execute for different schema? RRS feed

  • Question

  • I am using SQL Server 2008 R2, I have a schema [dbo], and in that schema, I have created a stored procedure dbo.GetAccount:

    SELECT * FROM tblAccount

    Then, I have created a schema [ABC] with user named UserABC.

    Now, I would like to login with UserABC and execute dbo.GetAccount for schema [ABC] to get all user of it and don't want to change code of dbo.GetAccount. So, how can I do?

    Thanks for your help.

    Thursday, August 21, 2014 7:20 AM

Answers

  • Hi,

    Tried creating the same under the stored procedure and yes it does work as you said .

    The statements in stored procedure run under the stored procedure's schema unless and until it has been mentioned specifially.

    I did try this as well.. EXEC('GetAccount') AS USER = 'UserABC'. Didn't work..

    The following worked. But I am not sure why you want to keep the stored procedure under the schema dbo.

    ALTER SCHEMA ABC TRANSFER dbo.GetAccount

    EXEC GetAccount

    Regards,

    Brindha.



    • Edited by Quest1234 Friday, August 22, 2014 10:41 AM
    • Proposed as answer by Johnny Bell Jnr Friday, August 22, 2014 3:02 PM
    • Marked as answer by Elvis Long Tuesday, September 2, 2014 1:59 AM
    Friday, August 22, 2014 10:41 AM

All replies

  • You can use the "EXECUTE AS" option for the SPROC. Please go thorugh the following.

    http://msdn.microsoft.com/en-IN/library/ms188354(v=sql.105).aspx

    Thursday, August 21, 2014 7:43 AM
  • I tried, this my code

    CREATE PROCEDURE dbo.GetAccount
    WITH EXECUTE AS 'UserABC'
    AS
    BEGIN
    	select * from tblAccountNet
    END
    GO

    but the result is data of dbo.AccountNet, not data of ABC.AccountNet. I would like to get data of ABC.AccountNet of UserABC

    Please help me. Thank you very much.


    Thursday, August 21, 2014 7:58 AM
  • Try setting the default schema for UserABC to ABC. I think the default schema is dbo right now because of which this is occuring. Please repost with whether this worked or not.

    Friday, August 22, 2014 7:29 AM
  • So you want regardless of whoever executes it the data to be pulled from ABC schema table?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, August 22, 2014 7:47 AM
  • @Avjit Swan, It's not working when setting the default schema for UserABC to ABC. 

    My goal is when I login with uername UserABC, in there, I execute procedure dbo.GetAccount and retured result is data of [ABC].tblAccountNet to optimize system's performance and don't want to create procedure for each schema.


    Friday, August 22, 2014 8:30 AM
  • @Visakh16, @Avjit Swan I know that if I use SCHEMA_NAME(), the problem will be resolved:

    ALTER PROCEDURE dbo.GetAccount
    AS
    BEGIN
    DECLARE @Sql nvarchar(MAX)
    SET @Sql = 'SELECT * FROM ' + SCHEMA_NAME() + '.tblAccountNet'
    EXEC(@Sql)

    But now, I have more 200 procedures, and in each procedure, I wrote about 1000 lines. I don't want to edit one by one. It's time consuming. Please help me.

    Thanks.






    Friday, August 22, 2014 8:47 AM
  • Hi

    First you should have created the schema and then the user. If it was the other way round, droping and recreating the user will help.

    Hope the table "tblAccountNet" was created under the schema 'ABC'. If not, try using the transfer statement to transfer it to the schema 'ABC', for example

    ALTER SCHEMA ABC TRANSFER dbo.tblAccountNet

    Regards,

    Brindha.

    Friday, August 22, 2014 9:13 AM
  • @Brindha, I execute ALTER SCHEMA ABC TRANSFER dbo.tblAccountNet and error is:

    Cannot transfer the object 'tblAccountNet', because it does not exist or you do not have permission.



    Friday, August 22, 2014 9:21 AM
  • Hi,

    Hope you have logged in using your dbo owner account, not with userABC

    Regards,

    Brindha.

    Friday, August 22, 2014 9:26 AM
  • @Brindha, It's not working

    ALTER PROCEDURE dbo.GetAccount
    AS
    BEGIN
    ALTER SCHEMA ABC TRANSFER dbo.tblAccountNet
    SELECT * FROM tblAccountNet
    END



    Friday, August 22, 2014 9:29 AM
  • Hi,

    I did try it by myself and worked.

    1. Created the schema

    2. created the user

    3. ALTER USER [testBrindha] WITH DEFAULT_SCHEMA = ABC;

    4. ALTER SCHEMA ABC TRANSFER dbo.tbltest  (the table tbltest was intially created under dbo)

    5. Login to the user testBrindha

    6. SELECT * FROM tbltest  - This statement worked

    7. SELECT * FROM dbo.tbltest - This errored which was expected.

    Not sure why its not working for you.

    Regards,

    Brindha.



    • Edited by Quest1234 Friday, August 22, 2014 9:46 AM
    Friday, August 22, 2014 9:45 AM
  • @Brindha, I login with UserABC, UserABC has [ABC] schema with table [ABC].AccountNet. I execute procedure dbo.GetAccount here, the result is data of [dbo].tblAccountNet.
    Friday, August 22, 2014 10:00 AM
  • Hi,

    Tried creating the same under the stored procedure and yes it does work as you said .

    The statements in stored procedure run under the stored procedure's schema unless and until it has been mentioned specifially.

    I did try this as well.. EXEC('GetAccount') AS USER = 'UserABC'. Didn't work..

    The following worked. But I am not sure why you want to keep the stored procedure under the schema dbo.

    ALTER SCHEMA ABC TRANSFER dbo.GetAccount

    EXEC GetAccount

    Regards,

    Brindha.



    • Edited by Quest1234 Friday, August 22, 2014 10:41 AM
    • Proposed as answer by Johnny Bell Jnr Friday, August 22, 2014 3:02 PM
    • Marked as answer by Elvis Long Tuesday, September 2, 2014 1:59 AM
    Friday, August 22, 2014 10:41 AM