Owner does not have permission

In the below script I creates a user “UserA” and then. UserA creates a table and then execute a SELECT statement on his table. The script to perform this task is following:

CREATE LOGIN UserA WITH PASSWORD = 'usera', CHECK_POLICY=OFF
GO
USE Test
GO
CREATE USER UserA
GO
sp_addrolemember 'db_ddladmin', N'UserA'
GO
EXECUTE AS USER = 'UserA'
GO
CREATE TABLE tblUserA (col1 INT)
GO
SELECT * FROM tblUserA
GO

Here we expect that SELECT statement should complete successfully because UserA is the owner and owner has all rights on an object.

But the result is an error that “

The SELECT permission was denied on the object 'tblUserA',

database 'Test', schema 'dbo'.”

Screenshot is following:

To get the answer of this problem, we have to understand some facts of name resolution:

1. When a user is created without specifying default schema then dbo is assigned as the default schema to that user, even the new user would not have permission on dbo schema.

2. When an object is created without specifying the schema then objects is created in the default schema of user.

Now we can understand our problem. Here we didn’t specify the schema to user UserA so the default schema is dbo. Then UserA created a table without specifying the schema so the table is created in dbo schema. But UserA does not have any permission on dbo schema and error returned.
Resolution: Below is the updated script that uses schema and this script executes successfully:

CREATE LOGIN UserA WITH PASSWORD = 'usera', CHECK_POLICY=OFF
GO
USE Test
GO
CREATE USER UserA
GO
sp_addrolemember 'db_ddladmin', N'UserA'
GO
CREATE SCHEMA SchemaA AUTHORIZATION UserA
GO
EXECUTE AS USER = 'UserA'
GO
CREATE TABLE SchemaA.tblUserA (col1 INT)
GO
SELECT * FROM SchemaA.tblUserA