Re: Schema Users and permissions

Hi Woodster

CREATE TABLE is a separate privilege, outside of the ability to ALTER any
particular schema.

GRANT CREATE TABLE TO <user or list-of-users or role>

Once someone has CREATE TABLE permission, they need permission to ALTER
whatever schema they are going to create a table in.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com


"TheWoodster" <TheWoodster@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:90EA120D-131A-436E-9235-E3B20E85B016@xxxxxxxxxxxxxxxx
I'm fairly new to MS-SQL administration but have been a DBA for Mainframe
DB's and for Oracle over many years. My problem is in trying to establish
a
secure development environment whereby each user has their own schema
which
they have full control and can grant privileges to others. I'm not sure
what
privileges I need to grant without giving too much authority.

We are running MS-SQL 2005 and are using Window Auth. I've created a
schema
for each individual user and have assigned it as their default. They are
also
the owner of their schema. However, when they try to create a table, they
get
a message saying they do not have the CREATE TABLE privilege. I would have
thought that as owner of the Schema they would have this authority.

I did find some information saying I needed to GRANT ALTER to the dbo
Schema
for each one. However, this did not seem to have an effect.

Has anyone done this and would be willing to share how they set it up?

--
Regards,
TheWoodster


.

Relevant Pages

  • Re: Yukon schemas
    ... ALTER on a schema is a very powerful permission and should not be granted ... Although ALTER on the schema will not allow the creation of new tables, ... is owned by dbo and granting ALTER on it will allow the grantees to create ...
    (microsoft.public.sqlserver.security)
  • Re: DDL Permissions - CREATE PROCEDURE, but no CREATE TABLE
    ... Have you tried to use only the REFERENCES permission on the table for the ... > schema, but not create or alter tables. ... you can't DENY DROP. ...
    (microsoft.public.sqlserver.security)
  • Re: Privileges Issue
    ... another schema to a another seperate schema. ... let's say my schema with the DBA role is called Schema 1. ... GRANT SELECT ON Schema2.tableA TO Schema3; ... The DBA role has the GRANT ANY PRIVILEGE privilege. ...
    (comp.databases.oracle.server)
  • Re: Schema Users and permissions
    ... the user has to have BOTH permission to CREATE TABLE and permission to ... ALTER SCHEMA in the schema where the tables are to be created. ... CREATE TABLE is a separate privilege, outside of the ability to ALTER any ...
    (microsoft.public.sqlserver.security)
  • Privileges Issue
    ... another schema to a another seperate schema. ... let's say my schema with the DBA role is called Schema 1. ... GRANT SELECT ON Schema2.tableA TO Schema3; ... The DBA role has the GRANT ANY PRIVILEGE privilege. ...
    (comp.databases.oracle.server)