Hi there,
I'm new to SQL 2005 and am having what is probably an easy problem.
I have created a user called testuser in a database called testdb.
The login has access to the testdb database and has a default schema
of dbo.
I then allowed testuser the ability to create tables in the testdb
database. However when i try to create a table in the database as
that user it comes up with a persmissions error
'the specified schema name dbo either does not exist or you do not
have permissions to use it'
Do I need to set some further permissons to allow this user to create
tables, is it not enough to give them the create table permisson on
the database ?
Any help would be much appricated.
Thanks
FranHi
You can add this user to db_owner database role.
USE dbtest
GO
-- Create a dbo table
CREATE TABLE dbo.t (id int)
GO
-- new user
CREATE LOGIN frant WITH password='jj'
CREATE USER frant FOR LOGIN frant WITH DEFAULT_SCHEMA =dbo
GRANT CREATE TABLE TO frant
GO
-- this fails, because frant does not have permissions on schema 'dbo'
SETUSER 'frant'
GO
CREATE TABLE t1 (id int)
GO
-- schema for frant
SETUSER
GO
CREATE SCHEMA frant_schema AUTHORIZATION frant
GO
SETUSER 'frant'
GO
CREATE TABLE t1 (id int)
GO
-- this works
CREATE TABLE frant_schema.t1 (id int)
GO
SETUSER
GO
<frant101@.googlemail.com> wrote in message
news:1183020722.016711.271170@.g4g2000hsf.googlegroups.com...
> Hi there,
> I'm new to SQL 2005 and am having what is probably an easy problem.
> I have created a user called testuser in a database called testdb.
> The login has access to the testdb database and has a default schema
> of dbo.
> I then allowed testuser the ability to create tables in the testdb
> database. However when i try to create a table in the database as
> that user it comes up with a persmissions error
> 'the specified schema name dbo either does not exist or you do not
> have permissions to use it'
> Do I need to set some further permissons to allow this user to create
> tables, is it not enough to give them the create table permisson on
> the database ?
> Any help would be much appricated.
> Thanks
> Fran
>|||Hi,
Thanks for the info, just trying to get my head round this. The CREATE
TABLE permisson doesnt give the user permisson to create the table
unless they have permisson on the schema as well ?
If for example i had three schema's
Sales
Accounting
Marketing
How would I create a user that could create a table in all three
schemas and also a user that could just create tables in Sales and
Accounting but not Marketing.
On last question how can i view a users or database role permissions
through Management Studio, if for example i click on the public
database role , nothing shows up
Thanks again for all your help
Fran|||Hi
One way is to create a database role that will containg those schemas . You
wil have to specify a schema in front of table.
USE [dbtest]
GO
CREATE ROLE [MyRoleNamer] AUTHORIZATION [username]
GO
USE [dbtest]
GO
ALTER AUTHORIZATION ON SCHEMA::[yourschema] TO [MyRoleNamer]
GO
ALTER AUTHORIZATION ON SCHEMA::[anotherschema] TO [MyRoleNamer]
select [Role] = r.name, [Member] = m.name
from sys.database_role_members rm
join sys.database_principals r on rm.role_principal_id = r.principal_id
join sys.database_principals m on
rm.member_principal_id = m.principal_id
order by r.name, m.name
<frant101@.googlemail.com> wrote in message
news:1183026306.569137.11310@.w5g2000hsg.googlegroups.com...
> Hi,
> Thanks for the info, just trying to get my head round this. The CREATE
> TABLE permisson doesnt give the user permisson to create the table
> unless they have permisson on the schema as well ?
> If for example i had three schema's
> Sales
> Accounting
> Marketing
> How would I create a user that could create a table in all three
> schemas and also a user that could just create tables in Sales and
> Accounting but not Marketing.
> On last question how can i view a users or database role permissions
> through Management Studio, if for example i click on the public
> database role , nothing shows up
> Thanks again for all your help
> Fran
>|||Thanks again, I guess then that even though a users default schema is
say the dbo schema it doesnt automatically mean they have permissons
on that schema. If for example i wanted to give a user the rights to
create a table in a schema but didnt want to run the Authorization
command (i'm guessing this command makes the user/role owner of that
schema) is there a way
Thanks
Fran|||> Thanks again, I guess then that even though a users default schema is
> say the dbo schema it doesnt automatically mean they have permissons
> on that schema.
Yes - the default schema only specifies the schema used for unqualified
object references. It does not grant permissions.
To create a table, ALTER permission on the schema are needed as well as
CREATE TABLE permissions in the database. For example:
GRANT CREATE TABLE TO MyUser;
GRANT ALTER ON SCHEMA::Sales TO MyUser;
GRANT ALTER ON SCHEMA::Accounting TO MyUser;
Hope this helps.
Dan Guzman
SQL Server MVP
<frant101@.googlemail.com> wrote in message
news:1183029214.769040.183400@.g4g2000hsf.googlegroups.com...
> Thanks again, I guess then that even though a users default schema is
> say the dbo schema it doesnt automatically mean they have permissons
> on that schema. If for example i wanted to give a user the rights to
> create a table in a schema but didnt want to run the Authorization
> command (i'm guessing this command makes the user/role owner of that
> schema) is there a way
> Thanks
> Fran
>|||Thanks Dan,
It confused me that you could add the create table permisson to a user
and they didnt have permissons to create a table structure ( seems a
bit strange to me)
If I wanted a user to be able to create tables in a database in all
schema's what is the best way to do that ?
and how can i view which users are in what database / server roles ?
Thanks
Francis|||See if this helps you
select [Role] = r.name, [Member] = m.name
from sys.database_role_members rm
join sys.database_principals r on rm.role_principal_id = r.principal_id
join sys.database_principals m on
rm.member_principal_id = m.principal_id
order by r.name, m.name
<frant101@.googlemail.com> wrote in message
news:1183034160.704628.192090@.w5g2000hsg.googlegroups.com...
> Thanks Dan,
> It confused me that you could add the create table permisson to a user
> and they didnt have permissons to create a table structure ( seems a
> bit strange to me)
> If I wanted a user to be able to create tables in a database in all
> schema's what is the best way to do that ?
> and how can i view which users are in what database / server roles ?
> Thanks
> Francis
>|||Hi Dan
But the user will still specify schema name to create a table. I understood
that the OP wanted to create a table without specifying the schema, as it
must ne a member at least of db_owner fixed database role
Just my two cents
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:32A22328-F7A8-4961-8605-8E6BD8CDCEE7@.microsoft.com...
> Yes - the default schema only specifies the schema used for unqualified
> object references. It does not grant permissions.
> To create a table, ALTER permission on the schema are needed as well as
> CREATE TABLE permissions in the database. For example:
> GRANT CREATE TABLE TO MyUser;
> GRANT ALTER ON SCHEMA::Sales TO MyUser;
> GRANT ALTER ON SCHEMA::Accounting TO MyUser;
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <frant101@.googlemail.com> wrote in message
> news:1183029214.769040.183400@.g4g2000hsf.googlegroups.com...
>|||(frant101@.googlemail.com) writes:
> If I wanted a user to be able to create tables in a database in all
> schema's what is the best way to do that ?
GRANT ALTER ANY SCHEMA TO <someuser>
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
