locked
permission issue RRS feed

  • Question

  • Hi,

    Need help in achieving the below requirement.

    I want to DENY the EXECUTE AS for a login. How to do that. Can anybody share their views??

    Here is what i have done.


    use master
    go
    create database testdb
    go


    -- creating a login
    USE [master]
    GO
    CREATE LOGIN [login1]
    WITH PASSWORD=N'test',
    DEFAULT_DATABASE=[master],
    CHECK_EXPIRATION=OFF,
    CHECK_POLICY=OFF
    GO

    USE testdb
    GO
    CREATE USER [login1] FOR LOGIN [login1]
    GO

    USE testdb
    GO
    EXEC sp_addrolemember N'db_owner', N'login1'
    GO


    -- create one more login with only "public" role
    USE [master]
    GO
    CREATE LOGIN [login2]
    WITH PASSWORD=N'test',
    DEFAULT_DATABASE=[master],
    CHECK_EXPIRATION=OFF,
    CHECK_POLICY=OFF
    GO

    USE testdb
    GO
    CREATE USER [login2] FOR LOGIN [login2]
    GO


    -- as "login1" created a stored procedure and granted executed the EXECUTE permission to "login2"
    created PROCEDURE dbo.test_proc
    (
    @p1 varchar(max)
    )
    WITH EXECUTE AS 'login1'
    AS
    BEGIN
    select SYSTEM_USER
    SELECT @@SERVERNAME
    END
    go

    -- grant execute permission to 'login2'
    GRANT EXECUTE on dbo.usp_test to login2;

    -- as login1
    exec dbo.test_proc 'dsgf'
    /* working fine */

    -- as login2
    exec dbo.test_proc 'dsgf'
    /* working fine */


    -- now to restrict the login2 to be impersonated as 'login1' done the following
    -- server level
    USE master;
    DENY IMPERSONATE ON LOGIN::login1 TO login2;
    GO

    -- database level
    USE testdb;
    go
    DENY IMPERSONATE ON USER::login1 TO login2;
    GO


    -- now tested as login2
    exec dbo.test_proc 'dsgf'
    /* working fine */

    But i dont want this behaviour to happen.... whenever 'login2' is trying to impersonate as 'login1', we should allow to do that. that's why am denying at server level and database level.

    IF i remove the EXECUTE permission, am able acheive what i need.
    But basically, if 'login2' is being denied at server level and database level, why it is not restricting the esxectin of the stored procedre.

    Now first I want understand what implicit permission's will be granted to a user who is having the EXECUTE permissions on a stored procedure.????????????

    I tried modifying the stored procedure, even it is able to execute bt i dont want that to happen.


    created PROCEDURE dbo.test_proc
    (
    @p1 varchar(max)
    )
    WITH EXECUTE AS CALLER;
    AS
    BEGIN
    select SYSTEM_USER
    SELECT @@SERVERNAME
    END

    go

    But if i say one of the below then it is working as i expected.

    use testdb
    go

    EXECUTE AS LOGIN = 'login1'
    SELECT SYSTEM_USER
    REVERT
    SELECT SYSTEM_USER
    go


    /*

    Msg 15406, Level 16, State 1, Line 1
    Cannot execute as the server principal because the principal "login1" does not exist, this type of principal cannot be impersonated, or you do not have permission.
    */


    Similarly,
    use testdb
    go
    EXECUTE AS USER = 'login1'
    SELECT USER_NAME()
    REVERT
    SELECT USER_NAME()
    go

    /*
    Msg 15517, Level 16, State 1, Line 1
    Cannot execute as the database principal because the principal "login1" does not exist, this type of principal cannot be impersonated, or you do not have permission.
    */

    This is what am expecting to behave whenever executing the stored procedure as well.

    -- as 'login2'
    use testdb
    go
    exec dbo.test_proc 'dsgf'
    go

    I want to know what implicit permissions will a login/dbuser gets if he has EXECUTE permission on a stored procedre????
    Can anyone answer this???

    Thanks in advance.

    Friday, May 13, 2011 4:48 AM

Answers

  • You can achieve this by deny permissions on proc to Login2 on database level,

    Deny execute on test_proc to login2.

    one deny stop executing even you have 100 grants !!!


    http://uk.linkedin.com/in/ramjaddu
    • Proposed as answer by Stephanie Lv Monday, May 23, 2011 1:24 AM
    • Marked as answer by Stephanie Lv Monday, May 23, 2011 11:59 PM
    Friday, May 13, 2011 10:19 AM

All replies

  • Hallo Manu,

    you'll find details concerning the privileges of EXECUTE AS in
    http://msdn.microsoft.com/en-en/library/ms188354.aspx


    Uwe Ricken

    MCIT Database Administrator 2005
    MCIT Database Administrator 2008
    MCTS SQL Server 2005
    MCTS SQL Server 2008, Implementation and Maintenance
    db Berater GmbH
    http://www-db-berater.de
    Friday, May 13, 2011 6:28 AM
  • Hi Ricken,

    I have gone throgh the link. Its worth reading it.

    However, i was looking for something specific reqirement for which i had post in the forum.

     

     

    Friday, May 13, 2011 6:48 AM
  • You can achieve this by deny permissions on proc to Login2 on database level,

    Deny execute on test_proc to login2.

    one deny stop executing even you have 100 grants !!!


    http://uk.linkedin.com/in/ramjaddu
    • Proposed as answer by Stephanie Lv Monday, May 23, 2011 1:24 AM
    • Marked as answer by Stephanie Lv Monday, May 23, 2011 11:59 PM
    Friday, May 13, 2011 10:19 AM