Re: Create table in schema

From: "Tom Moreau" <tom@xxxxxxxxxxxxxxxxxxxx>
Date: Thu, 9 Aug 2007 14:37:06 -0400

Just trying to narrow things down. Try:

grant CONTROL on schema::dds to A_Role

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"Mitch" <Mitch@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F256CCAE-6487-47B9-8F38-96FBC5869F14@xxxxxxxxxxxxxxxx
Sorry, that was just a typo in my mail. It's all dds_pco_role, not A_role.

"Tom Moreau" wrote:

You granted the CREATE TABLE to dds_pco_role - not A_role.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"Mitch" <Mitch@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8A2278E0-9069-4CF2-A338-7E196E49FF7F@xxxxxxxxxxxxxxxx
Hi, I'm trying to create a schema, and have the userid assigned to a role
have the ability to create tables just in this schema. I have created a
role
A_Role and want to assign all the permissions to the role. So I tried the
commands:

grant alter on schema::dds to A_Role
grant create table to dds_pco_role

Then I've added the userid to this role. Logged in as the user, but when
I
try to create a table

create table dds.T1 (col1 int, col2 char(3))

I get the message:

The specified schema name "dds" either does not exist or you do not have
permission to use it.

And yes, the schema does exist. Am I missing another command?

Thanks,
Mitche

Follow-Ups:
- Re: Create table in schema
  - From: Mitch

References:
- Re: Create table in schema
  - From: Tom Moreau

Prev by Date: Re: User can't be deleted because it has granted/denied permissions on objects
Next by Date: Re: Create table in schema
Previous by thread: Re: Create table in schema
Next by thread: Re: Create table in schema
Index(es):
- Date
- Thread

Relevant Pages

Re: Create table in schema
... SQL Server MVP ... "Tom Moreau" wrote: ... Every time I try to grant control to the dds_pco_role, ... EXEC sys.sp_executesql N'CREATE SCHEMA AUTHORIZATION ...
(microsoft.public.sqlserver.security)
Re: Create table in schema
... "Tom Moreau" wrote: ... SQL Server MVP ... Every time I try to grant control to the dds_pco_role, ... I also got that when I ran grant alter on schema. ...
(microsoft.public.sqlserver.security)
Re: Create table in schema
... SQL Server MVP ... Every time I try to grant control to the dds_pco_role, ... Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, ... I also got that when I ran grant alter on schema. ...
(microsoft.public.sqlserver.security)
Re: Create table in schema
... SQL Server MVP ... I also got that when I ran grant alter on schema. ... A_Role and want to assign all the permissions to the role. ...
(microsoft.public.sqlserver.security)
Re: Create table in schema
... SQL Server MVP ... grant CONTROL on schema::dds to A_Role ... start a brand new window and log in as dds_user. ... I also got that when I ran grant alter on schema. ...
(microsoft.public.sqlserver.security)