2

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?

migrated from stackoverflow.com Oct 13 '12 at 22:29

This question came from our site for professional and enthusiast programmers.

  • Does it fail with a message saying "permissions failure", or does it actually include useful information in the error message? Also, have you applied any other permissions to this user? – Damien_The_Unbeliever Oct 12 '12 at 13:20
2

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

Your Answer

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