Posted by Erland Sommarskog on 09/06/07 07:20

gdev (paul.afamdi.okeke@gmail.com) writes:
> Having some trouble getting my head around setting access to specific
> schemas- here's my problem:
>
> I've created a specific schema that I only want certain users to
> control
>
>
> Problem: Even though I give them full access....the cannot create
> tables under that schema...my code is below (flyer is the schema,
> eflyerAdmin is the role, and eflyer is the user):
>
> GRANT
> ALTER,
> CONTROL,
> DELETE,
> EXECUTE,
> INSERT,
> REFERENCES,
> SELECT,
> TAKE OWNERSHIP,
> UPDATE,
> VIEW DEFINITION
> ON SCHEMA::flyer
> TO eflyerAdmin
> GO
> -- Add an existing user to the role
> EXEC sp_addrolemember N'eflyerAdmin', N'eflyer'

You also need:

GRANT CREATE TABLE TO eflyerAdmin

And it's sufficient to grant CONTROL on the schema, since CONTROL implies
the rest.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: sql rules and udt
• Prev in forum: Re: Dynamic Columns
• Thread view: Re: Help needed: Granting Create table permisions on specific Schema Options

[Reply to this message]