Michael Hotek


I know I'm missing something obvious and most likely just need some sleep to figure it out,but I'm trying to finish a block of auditing code off to hit a deadline. I can't figure out why I get an error when I try to do the following:

CREATE USER audituser WITHOUT LOGIN

GO

CREATE SCHEMA audit AUTHORIZATION audituser

GO

EXECUTE AS USER = 'audituser'

GO

--Create new, flexible audit structure

CREATE TABLE audit.AuditTable

(AuditID int identity(1,1),

TableName sysname NOT NULL,

AuditAction char(2) NOT NULL,

RowData xml NOT NULL,

AuditDate datetime NOT NULL CONSTRAINT df_table1_audit_editdate DEFAULT (getdate()),

AuditUser varchar(30) NOT NULL CONSTRAINT df_table1_audit_edituser DEFAULT (suser_sname()),

CONSTRAINT pk_table1_audit PRIMARY KEY CLUSTERED (AuditID))

GO

The user and schema are created properly. audituser is the owner of the schema and therefore should have the authority to do anything at all within the schema. However, the create table statement is failing and saying CREATE TABLE permission denied. Why





Re: Simple authorization problem

Raul Garcia - MS


It is necessary to have CREATE TABLE permission in addition to having the right permissions on the schema where you are trying to create such table. Here you have at least the following two choices:

1) Grant CREATE TABLE to audituser. That way audituser will be able to create any table needed as long as the user has the right permissions on the schema.

2) Using a privileged user (i.e. dbo) create the table on audituser schema. By default the table will be owned by the owner of the schema (you can verify it on sys.objects).

I hope this information helps,

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine