Could not obtain information about Windows NT group/user

dbtut

6 years ago

You should receive this error when you use “Execute as ower” in your script or in your jobs. I received this error when I want to create a natively compiled stored procedure for my memory optimized table. You may want to read the below article about memory optimized tables and natively compiled stored procedures.

“In Memory OLTP in SQL Server“,

“What is Natively Compiled Stored Procedure in SQL Server”

The reason I receive the error is that I used the expression “Execute as Owner” in the create script and the database owner was not “sa”.

The script and the error is below:

USE [Test]
GO
CREATE PROCEDURE NativelyCompiledSP_Example
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
DECLARE @i INT = 0
WHILE @i < 50000
BEGIN
INSERT INTO dbo.MemoryOptimizedTable_Example VALUES (@i,N'My_Name')
SET @i += 1
END
END
GO

USE [Test]

CREATE PROCEDURE NativelyCompiledSP_Example

WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER

BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')

DECLARE @i INT = 0

WHILE @i < 50000

BEGIN

INSERT INTO dbo.MemoryOptimizedTable_Example VALUES (@i,N'My_Name')

SET @i += 1

END

To solve the error, change the owner of the database or job to sa. You should read the below article to change the owner of the database.

“How To Change Owner of Database in SQL Server(ALTER AUTHORIZATION)”