Re: Create table in schema



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
    ... 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)
  • Re: whats is wrong here????
    ... you can grant a role execute permissions on all objects on a schema with a single GRANT. ...
    (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)