Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

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

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

1 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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.