Sql-server – SQL Server Schema Security

Securitysql server

I have a user U in a database where I want it to be able to create types and stored procs in a specified schema say UserSchema but not be able to alter schema dbo. So I created the schema and also applied the following grant statement to it:

GRANT  ALTER, Execute ON SCHEMA ::UserSchema to [U];

When I login as user U and I try create a type in UserSchema it fails with a permissions failure…

Any Ideas?

Best Answer

That's because you also need to grant the CREATE TYPE permission on the database to the particular principle(s).

Please see this BOL reference on CREATE TYPE:

Requires CREATE TYPE permission in the current database and ALTER permission on schema_name.

grant create type
to [U];
go
Related Question