sproc with execute as sqlusr fails !

  • Trying to make my application a bit more secure, I wanted to alter all sprocs to execure as a db sqluser without login.

    On execute I received this error :

    Msg 8152, Level 16, State 2, Procedure usp_Isrt_ExecAsBug, Line 10

    String or binary data would be truncated.

    The statement has been terminated.

    I reported this bug at Connect:

    https://connect.microsoft.com/SQLServer/feedback/details/529771/sproc-with-execute-as-sqluser-without-login-results-in-string-would-be-truncated

    USE [master]

    GO

    Select @@version as [@@version]

    go

    CREATE LOGIN [LoginExecAsBug] WITH PASSWORD=N's€cr@', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

    GO

    /****** Object: Database [DExecAsBug] Script Date: 02/01/2010 18:32:28 ******/

    CREATE DATABASE [DExecAsBug] ;

    Print 'Database [DExecAsBug] created.'

    go

    Exec sp_dbcmptlevel @dbname = 'DExecAsBug' , @new_cmptlevel = 90 ;

    go

    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

    begin

    EXEC [DExecAsBug].[dbo].[sp_fulltext_database] @action = 'enable'

    end

    GO

    ALTER DATABASE [DExecAsBug] SET ANSI_NULL_DEFAULT OFF

    GO

    ALTER DATABASE [DExecAsBug] SET ANSI_NULLS OFF

    GO

    ALTER DATABASE [DExecAsBug] SET ANSI_PADDING OFF

    GO

    ALTER DATABASE [DExecAsBug] SET ANSI_WARNINGS OFF

    GO

    ALTER DATABASE [DExecAsBug] SET ARITHABORT OFF

    GO

    ALTER DATABASE [DExecAsBug] SET AUTO_CLOSE OFF

    GO

    ALTER DATABASE [DExecAsBug] SET AUTO_CREATE_STATISTICS ON

    GO

    ALTER DATABASE [DExecAsBug] SET AUTO_SHRINK OFF

    GO

    ALTER DATABASE [DExecAsBug] SET AUTO_UPDATE_STATISTICS ON

    GO

    ALTER DATABASE [DExecAsBug] SET CURSOR_CLOSE_ON_COMMIT OFF

    GO

    ALTER DATABASE [DExecAsBug] SET CURSOR_DEFAULT GLOBAL

    GO

    ALTER DATABASE [DExecAsBug] SET CONCAT_NULL_YIELDS_NULL OFF

    GO

    ALTER DATABASE [DExecAsBug] SET NUMERIC_ROUNDABORT OFF

    GO

    ALTER DATABASE [DExecAsBug] SET QUOTED_IDENTIFIER OFF

    GO

    ALTER DATABASE [DExecAsBug] SET RECURSIVE_TRIGGERS OFF

    GO

    ALTER DATABASE [DExecAsBug] SET ENABLE_BROKER

    GO

    ALTER DATABASE [DExecAsBug] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

    GO

    ALTER DATABASE [DExecAsBug] SET DATE_CORRELATION_OPTIMIZATION OFF

    GO

    ALTER DATABASE [DExecAsBug] SET TRUSTWORTHY OFF

    GO

    ALTER DATABASE [DExecAsBug] SET ALLOW_SNAPSHOT_ISOLATION OFF

    GO

    ALTER DATABASE [DExecAsBug] SET PARAMETERIZATION SIMPLE

    GO

    ALTER DATABASE [DExecAsBug] SET READ_COMMITTED_SNAPSHOT OFF

    GO

    ALTER DATABASE [DExecAsBug] SET READ_WRITE

    GO

    ALTER DATABASE [DExecAsBug] SET RECOVERY FULL

    GO

    ALTER DATABASE [DExecAsBug] SET MULTI_USER

    GO

    ALTER DATABASE [DExecAsBug] SET PAGE_VERIFY CHECKSUM

    GO

    ALTER DATABASE [DExecAsBug] SET DB_CHAINING OFF

    GO

    USE DExecAsBug

    GO

    CREATE USER [UsrExecAsBug] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]

    GO

    EXEC sp_addrolemember N'db_datareader', N'UsrExecAsBug'

    GO

    EXEC sp_addrolemember N'db_datawriter', N'UsrExecAsBug'

    GO

    CREATE USER [LoginExecAsBug] FOR LOGIN [LoginExecAsBug] WITH DEFAULT_SCHEMA=[dbo]

    GO

    EXEC sp_addrolemember N'db_datareader', N'LoginExecAsBug'

    GO

    EXEC sp_addrolemember N'db_datawriter', N'LoginExecAsBug'

    GO

    /****** Object: Table [dbo].[T_ExecAsBug] Script Date: 02/01/2010 18:10:31 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[T_ExecAsBug](

    [IdNr] [int] IDENTITY(1,1) NOT NULL,

    [Name] [varchar](50) NOT NULL,

    [TsCrea] [datetime] NOT NULL,

    [UserCrea] [varchar](30) NOT NULL,

    [TsModif] [datetime] NOT NULL,

    [UserModif] [varchar](30) NOT NULL,

    CONSTRAINT [PK__T_ExecAsBug__0EA330E9] PRIMARY KEY CLUSTERED

    (

    [IdNr] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    ALTER TABLE [dbo].[T_ExecAsBug] ADD CONSTRAINT [DF__T_ExecAsBug__TsCre__3A4CA8FD] DEFAULT (getdate()) FOR [TsCrea]

    GO

    ALTER TABLE [dbo].[T_ExecAsBug] ADD CONSTRAINT [DF__T_ExecAsBug__UserC__3B40CD36] DEFAULT (suser_sname()) FOR [UserCrea]

    GO

    ALTER TABLE [dbo].[T_ExecAsBug] ADD CONSTRAINT [DF__T_ExecAsBug__TsAlt__3C34F16F] DEFAULT (getdate()) FOR [TsModif]

    GO

    ALTER TABLE [dbo].[T_ExecAsBug] ADD CONSTRAINT [DF__T_ExecAsBug__UserA__3D2915A8] DEFAULT (suser_sname()) FOR [UserModif]

    GO

    CREATE PROC [dbo].[usp_Isrt_ExecAsBug]

    @Name varchar(50)

    AS

    Begin

    SET NOCOUNT ON

    INSERT INTO dbo.T_ExecAsBug (

    [Name]

    )

    VALUES (

    @Name

    )

    SELECT SCOPE_IDENTITY() As InsertedID

    end

    go

    Print 'This one is OK'

    exec [usp_Isrt_ExecAsBug] 'TestOK'

    /*

    This one is OK

    InsertedID

    ---------------------------------------

    1

    */

    GO

    ALTER PROC [dbo].[usp_Isrt_ExecAsBug]

    @Name varchar(50)

    with execute as 'UsrExecAsBug'

    AS

    Begin

    SET NOCOUNT ON

    INSERT INTO dbo.T_ExecAsBug (

    [Name]

    )

    VALUES (

    @Name

    )

    SELECT SCOPE_IDENTITY() As InsertedID

    end

    go

    Print 'This one fails !'

    exec [usp_Isrt_ExecAsBug] 'Test_NOT_OK'

    /*

    This one fails !

    Msg 8152, Level 16, State 2, Procedure usp_Isrt_ExecAsBug, Line 10

    String or binary data would be truncated.

    The statement has been terminated.

    InsertedID

    ---------------------------------------

    NULL

    */

    go

    ALTER PROC [dbo].[usp_Isrt_ExecAsBug]

    @Name varchar(50)

    with execute as 'LoginExecAsBug'

    AS

    Begin

    SET NOCOUNT ON

    INSERT INTO dbo.T_ExecAsBug (

    [Name]

    )

    VALUES (

    @Name

    )

    SELECT SCOPE_IDENTITY() As InsertedID

    end

    go

    Print 'This works fine 2!'

    exec [usp_Isrt_ExecAsBug] 'Test_OK_2'

    /*

    This works fine 2!

    InsertedID

    ---------------------------------------

    3

    */

    go

    ALTER PROC [dbo].[usp_Isrt_ExecAsBug]

    @Name varchar(50)

    with execute as owner

    AS

    Begin

    SET NOCOUNT ON

    INSERT INTO dbo.T_ExecAsBug (

    [Name]

    )

    VALUES (

    @Name

    )

    SELECT SCOPE_IDENTITY() As InsertedID

    end

    go

    Print 'This works fine 3!'

    exec [usp_Isrt_ExecAsBug] 'Test_OK_3'

    /*

    This works fine 3!

    InsertedID

    ---------------------------------------

    4

    */

    go

    Select *

    from dbo.T_ExecAsBug

    order by IdNr

    go

    use master

    go

    drop database DExecAsBug;

    print 'Database [DExecAsBug] Dropped !'

    go

    drop LOGIN [LoginExecAsBug] ;

    go

    /* full TEXT result :

    @@version

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)

    Nov 24 2008 13:01:59

    Copyright (c) 1988-2005 Microsoft Corporation

    Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

    (1 row(s) affected)

    Database [DExecAsBug] created.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    This one is OK

    InsertedID

    ---------------------------------------

    1

    This one fails !

    Msg 8152, Level 16, State 2, Procedure usp_Isrt_ExecAsBug, Line 10

    String or binary data would be truncated.

    The statement has been terminated.

    InsertedID

    ---------------------------------------

    NULL

    This works fine 2!

    InsertedID

    ---------------------------------------

    3

    This works fine 3!

    InsertedID

    ---------------------------------------

    4

    IdNr Name TsCrea UserCrea TsModif UserModif

    ----------- -------------------------------------------------- ----------------------- ------------------------------ ----------------------- ------------------------------

    1 TestOK 2010-02-01 18:58:13.297 mydbo 2010-02-01 18:58:13.297 mydbo

    3 Test_OK_2 2010-02-01 18:58:13.440 LoginExecAsBug 2010-02-01 18:58:13.440 LoginExecAsBug

    4 Test_OK_3 2010-02-01 18:58:13.507 mydbo 2010-02-01 18:58:13.507 mydbo

    (3 row(s) affected)

    Database [DExecAsBug] Dropped !

    */

    Can you confirm the bug ? ( Vote on Connect )

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • the problem lies in the details... your default constraint for the usermodif is trying to insert the sid of the execute as user but is failiing cause the column length of 30 doesnt provide enough room. Increade the column size and youre fine.

  • You are correct !!

    Apparently it doesn't store the UserName, but the its internal identifier 'S-1-9-3-3482764364-1226597329-2493949361-1082924894'.

    I wonder why they would do that !!

    They should have documented this behaviour.

    In my test I didn't make the column that large.

    Thank you for testing this and providing the feedback.

    [edited]

    So the solution for my case is :

    1) As suggested : use datatype "sysname" and accept the (useless) internal identifier.

    2) enlarge the column and accept the (useless) internal identifier.

    3) alter the default constraints to :

    ALTER TABLE [dbo].[T_ExecAsBug] ADD CONSTRAINT [DF__T_ExecAsBug__UserC__3B40CD36] DEFAULT case when datalength(suser_sname()) > 30 then user_name() else suser_sname() end FOR [UserCrea]

    4) alter the default constraints to use ORIGINAL_LOGIN() and provide the correct feedback on Who actually modified the data.

    I'll go for option 4. (because in my domain the length of the username doesn't exceed 30 bytes.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • nm

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply