Re: Create table in schema

Same thing.

Have I set up the role incorrectly? Here's my script

IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = 'dds_user')
CREATE LOGIN [dds_user] WITH PASSWORD='DDSm@st3r', CHECK_EXPIRATION=OFF

IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name =
'dds_user' and type = 'S')
CREATE USER [dds_user] FOR LOGIN [dds_user]

IF EXISTS(select name from sys.database_principals where name =
'dds_pco_role' and type = 'R')
DROP ROLE dds_pco_role

CREATE ROLE dds_pco_role AUTHORIZATION dds_user

IF NOT EXISTS(select name from sys.schemas where name = 'dds')
EXEC sys.sp_executesql N'CREATE SCHEMA [dds] AUTHORIZATION [dds_pco_role]'

EXEC sp_addrolemember 'dds_pco_role', 'dds_user'

-- grants for dds schema
grant control on schema::dds to dds_pco_role
grant create table to dds_pco_role

Thanks!

"Tom Moreau" wrote:

OK, I'd just log out and back in as sa, just to be sure we're starting
clean. Then, run:

grant CONTROL on schema::dds to A_Role

After that, start a brand new window and log in as dds_user. Try creating
the table then.

--
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:FE2FB63C-1DB2-4C21-B072-06FF92DCF8F2@xxxxxxxxxxxxxxxx
No, there's no EXECUTE AS in my script. And when I run select current_user
in the window that I'm trying to grant the rights (logged in as SA), I get
"dbo."

When I run select current_user in the window I'm trying to create the table,
logged in as dds_user, I get "dds_user."

"Tom Moreau" wrote:

I think you maybe had run EXECUTE AS and didn't run REVERT. Thus, it
thinks
you are the user you're pretending to be.

Run:

SELECT CURRENT_USER

and see what it says.

--
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:18DF8BA6-E12C-4363-BEEA-5169633F66D1@xxxxxxxxxxxxxxxx
I'm getting:

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner,
information_schema, sys, or yourself.

I also got that when I ran grant alter on schema. What does it mean?

"Tom Moreau" wrote:

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









.

Relevant Pages

  • 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 ... "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: Yukon schemas
    ... ALTER to the schema. ... you have to grant create permission to perform the action ... data and to create and alter stored procedures and views that they owned. ...
    (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)