Re: Create table in schema



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 ... 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)
  • Re: SSMS 2005 - Value does not fall within the expected range. (SqlMgm
    ... name as the Database Role the securables are shown. ... -- SCHEMA Restrictions ... GRANT CREATE PROCEDURE TO; ... -- Defined for each stored procedure need by Role. ...
    (microsoft.public.sqlserver.tools)
  • 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)