February 1, 2010 at 11:21 am
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:
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
February 1, 2010 at 2:17 pm
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.
February 2, 2010 at 12:06 am
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
February 2, 2010 at 1:08 am
nm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply