Create table in schema

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 rol
e
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,
MitcheYou 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@.discussions.microsoft.com> wrote in message
news:8A2278E0-9069-4CF2-A338-7E196E49FF7F@.microsoft.com...
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|||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@.discussions.microsoft.com> wrote in message
> news:8A2278E0-9069-4CF2-A338-7E196E49FF7F@.microsoft.com...
> 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
>
>|||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@.discussions.microsoft.com> wrote in message
news:F256CCAE-6487-47B9-8F38-96FBC5869F14@.microsoft.com...
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@.discussions.microsoft.com> wrote in message
> news:8A2278E0-9069-4CF2-A338-7E196E49FF7F@.microsoft.com...
> 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
>
>|||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@.discussions.microsoft.com> wrote in message
> news:F256CCAE-6487-47B9-8F38-96FBC5869F14@.microsoft.com...
> Sorry, that was just a typo in my mail. It's all dds_pco_role, not A_role
.
> "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@.discussions.microsoft.com> wrote in message
news:18DF8BA6-E12C-4363-BEEA-5169633F66D1@.microsoft.com...
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@.discussions.microsoft.com> wrote in message
> news:F256CCAE-6487-47B9-8F38-96FBC5869F14@.microsoft.com...
> Sorry, that was just a typo in my mail. It's all dds_pco_role, not
> A_role.
> "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@.discussions.microsoft.com> wrote in message
news:FE2FB63C-1DB2-4C21-B072-06FF92DCF8F2@.microsoft.com...
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@.discussions.microsoft.com> wrote in message
> news:18DF8BA6-E12C-4363-BEEA-5169633F66D1@.microsoft.com...
> 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:
>
>|||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@.discussions.microsoft.com> wrote in message
> news:FE2FB63C-1DB2-4C21-B072-06FF92DCF8F2@.microsoft.com...
> No, there's no EXECUTE AS in my script. And when I run select current_use
r
> 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 tabl
e,
> logged in as dds_user, I get "dds_user."
> "Tom Moreau" wrote:
>
>|||This all worked for me. What I did was run the entire script in
AdventureWorks. I then opened a new window and then ran:
execute as user = 'dds_user'
Then, I ran:
create table dds.T1 (col1 int, col2 char(3))
It executed OK.
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@.discussions.microsoft.com> wrote in message
news:BB085D07-78A2-4DB6-A04B-BD56E11FB370@.microsoft.com...
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@.discussions.microsoft.com> wrote in message
> news:FE2FB63C-1DB2-4C21-B072-06FF92DCF8F2@.microsoft.com...
> 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 don't get it. What version of sql are you running?
Every time I try to grant control to the dds_pco_role, I get the message:
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner,
information_schema, sys, or yourself.
And then I check the permissions in the sys.database_permissions table, and
that permission is not there. I don't get why it's not working?!?!?!
"Tom Moreau" wrote:

> This all worked for me. What I did was run the entire script in
> AdventureWorks. I then opened a new window and then ran:
> execute as user = 'dds_user'
> Then, I ran:
> create table dds.T1 (col1 int, col2 char(3))
> It executed OK.
>
> --
> 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@.discussions.microsoft.com> wrote in message
> news:BB085D07-78A2-4DB6-A04B-BD56E11FB370@.microsoft.com...
> 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=OF
F
> 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_pc
o_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:
>
>sql