Re: Create table in schema



Microsoft SQL Server 2005 - 9.00.3153.00 (X64)

"Tom Moreau" wrote:

I'm using SQL Server 2005 Developer Edition. What version and service pack
level are you using? Run:

select @@version

--
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:2578D6EB-9CA1-4A32-8420-47EBADA70E18@xxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BB085D07-78A2-4DB6-A04B-BD56E11FB370@xxxxxxxxxxxxxxxx
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
    ... 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: ... grant CONTROL on schema::dds to A_Role ... SQL Server MVP ... 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)