467,879 Members | 1,236 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,879 developers. It's quick & easy.

Help needed: Granting Create table permisions on specific Schema Options



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'

Sep 6 '07 #1
  • viewed: 3877
Share:
1 Reply
gdev (pa***************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 6 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by DC_DBA | last post: by
9 posts views Thread by Tony Girgenti | last post: by
reply views Thread by Julie Warden | last post: by
reply views Thread by SOI_0152 | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.