none
Altering the Execution Context of Stored Procedures RRS feed

Answers

  • Looks like somebody wants to use EXECUTE AS clause to switch execution context.

    This command enables to allow users to impersonate highly privileged accounts only when needed and revert back to the original execution context with limited privileges


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, May 15, 2014 6:06 AM
  • Hi rmohan,
    In T-SQL, we can specify the context for a module by using the EXECUTE AS clause so that we can control which user account the Database Engine uses to validate permissions on objects that are referenced by the module. Generally, we specify a login or user that has the least privileges required to perform the operations defined in the module. Besides, there are some permissions considerations when using the EXECUTE AS clause:

    • To specify the EXECUTE AS clause when you create or modify a module, you must have IMPERSONATE permissions on the specified principal and also permissions to create the module.
    • To execute a module specified with EXECUTE AS, the caller must have EXECUTE permissions on the module.
    • To execute a CLR module specified with EXECUTE AS that accesses resources in another database or server, the target database or server must trust the authenticator of the database from which the module originates (the source database).

    Here are two examples for your reference:

    USE TestDB;
    GO
    CREATE PROCEDURE dbo.Demo
    WITH EXECUTE AS 'Domain\User'
    AS
    SELECT user_name()   -- Shows execution context is set to Domain\User.
    GO

    User TestDB;
    GO
    CREATE PROCEDURE dbo.Demo WITH EXECUTE AS 'SqlUser1' AS SELECT user_name(); -- Shows execution context is set to SqlUser1. EXECUTE AS CALLER; SELECT user_name(); -- Shows execution context is set to SqlUser2, the caller of the module. REVERT; SELECT user_name(); -- Shows execution context is set to SqlUser1. GO

    References:

    Regards,


    Mike Yin
    TechNet Community Support

    Wednesday, May 21, 2014 6:32 AM
    Moderator

All replies

  • Looks like somebody wants to use EXECUTE AS clause to switch execution context.

    This command enables to allow users to impersonate highly privileged accounts only when needed and revert back to the original execution context with limited privileges


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, May 15, 2014 6:06 AM
  • Could please explain bit more 

    Thanks 

    Thursday, May 15, 2014 6:22 AM
  • Hi rmohan,
    In T-SQL, we can specify the context for a module by using the EXECUTE AS clause so that we can control which user account the Database Engine uses to validate permissions on objects that are referenced by the module. Generally, we specify a login or user that has the least privileges required to perform the operations defined in the module. Besides, there are some permissions considerations when using the EXECUTE AS clause:

    • To specify the EXECUTE AS clause when you create or modify a module, you must have IMPERSONATE permissions on the specified principal and also permissions to create the module.
    • To execute a module specified with EXECUTE AS, the caller must have EXECUTE permissions on the module.
    • To execute a CLR module specified with EXECUTE AS that accesses resources in another database or server, the target database or server must trust the authenticator of the database from which the module originates (the source database).

    Here are two examples for your reference:

    USE TestDB;
    GO
    CREATE PROCEDURE dbo.Demo
    WITH EXECUTE AS 'Domain\User'
    AS
    SELECT user_name()   -- Shows execution context is set to Domain\User.
    GO

    User TestDB;
    GO
    CREATE PROCEDURE dbo.Demo WITH EXECUTE AS 'SqlUser1' AS SELECT user_name(); -- Shows execution context is set to SqlUser1. EXECUTE AS CALLER; SELECT user_name(); -- Shows execution context is set to SqlUser2, the caller of the module. REVERT; SELECT user_name(); -- Shows execution context is set to SqlUser1. GO

    References:

    Regards,


    Mike Yin
    TechNet Community Support

    Wednesday, May 21, 2014 6:32 AM
    Moderator