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

Create Login like another login...

One bit of functionality I like in Oracle's Enterprise manager, that I
haven't located in SQL Server is the ability to create a new login with all
the same rights, roles etc of an existing user.
I am using SQL Server 2005. I have tried
USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LO
GIN written by GREG LARSEN, but I ge
t
an error when executing it.
==================
Msg 213, Level 16, State 7, Line 2
Insert Error: Column name or number of supplied values does not match table
definition.
==================
Does anyone point me to a way of doing this?
PaulThat's a pretty old script you're using and it's written for SQL Server
2000, calling objects in the master database.
You can update the script using some of the security catalog views instead
of tables in master. For example, fn_my_permissions will tell you what
effective permissions the current user has.
As for the error message below, it looks like you probably had a typo
somewhere when creating or running the sproc. Probably a datatype mismatch.
joe.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:CFECA19B-9C34-4D52-B9FC-1FA95817EE78@.microsoft.com...
> One bit of functionality I like in Oracle's Enterprise manager, that I
> haven't located in SQL Server is the ability to create a new login with
> all
> the same rights, roles etc of an existing user.
> I am using SQL Server 2005. I have tried
> USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LO
GIN written by GREG LARSEN, but I
> get
> an error when executing it.
> ==================
> Msg 213, Level 16, State 7, Line 2
> Insert Error: Column name or number of supplied values does not match
> table
> definition.
> ==================
> Does anyone point me to a way of doing this?
> Paul

Create folders in Object Explorer - wish...

Here's one thing that I'd like to see come out in some version of the SQL Server Management Studio...

The ability to create folders under the database node so that databases can be grouped on one server.

We have over 100 databases on our development server and these are created by a range of consultants and developers and even support staff as needed.

Being able to group the databases by product, etc would be a nice touch since we have client databases that don't fit naming conventions etc.

Multiple instances are another way around this but are expensive and resource hungry - we develop and support models, not use them for transactions too much.

Yes there are 'better' ways such as setting security correctly but we are too busy working and not maintaining.

Folders or database groups would be a nice touch.

Cheers

I encourage you to offer the suggestion here, and to encourage others so inclined to 'vote' for it. (Enhancement suggestions run somewhat like a popularity contest.

Suggestions for SQL Server

http://connect.microsoft.com/sqlserver