0

I'm trying to get a user to create tables in a schema that is set as their default schema and they are set as the owner of the schema.

When I run this command, even though their default schema isn't [dbo], it still creates the table under the [dbo] schema

EXECUTE AS USER = 'test'

CREATE TABLE TEST (T VARCHAR(10))

REVERT

The reason I'm looking to do this, is because the program that we're using, by default, uses the [dbo] schema to replicate data. It states in the documentation that tables will use the schema prefix of which user is running the stored procedure.

Is this possible? Any new tables created by this user, I want it to use their default schema instead of [dbo]

2
  • 1
    Maybe a limitation of EXECUTE AS? I would have thought it would use their default schema here – Martin Smith Jan 8 at 19:44
  • No. See answer for example. – David Browne - Microsoft Jan 9 at 17:14
3

This is possible - POC below shown from SQL Server 2019

USE [Test]
GO

CREATE USER [TestSchemaUser] 
WITHOUT LOGIN
GO

ALTER ROLE [db_ddladmin]
ADD MEMBER [TestSchemaUser]
GO

CREATE SCHEMA [TestSchema]
AUTHORIZATION [TestSchemaUser]
GO

ALTER USER [TestSchemaUser] WITH DEFAULT_SCHEMA=[TestSchema]
GO

EXECUTE AS USER = 'TestSchemaUser'
GO

CREATE TABLE T1([C1] INT)
GO

REVERT

SELECT S.name [Schema], T.name [Table]
FROM sys.tables T
    JOIN sys.schemas S on T.schema_id = S.schema_id
WHERE T.name = 'T1'

Query Results

1
  • Thanks for this. Your scenario worked but when I plug mine into this code, it still doesn't want to work. I have an existing table with the schema in it. I'll probably have to delete any reference to the schema, then re-create everything using your suggestion. – jdids Jan 8 at 21:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.