sql server 2005 security and schemas: an example please?

Let me establish the following didactic escenario:

There are 1000 users registered in the active directory , 50 of them are
going to use an application that is not developed yet. They belong to
different departments.

Only the people of the TI support department can use the "sa" account of the
sql server.

The group of developers has 3 members. They must be provided with
privileges for the sql development in the base (create-alter-etc.) for
tables, views, stored procedures, etc.

The final users must not have privileges to do that, they will only read and
write data in tables, views, and execute stored procedures.

During the development, the developers sould test the functionallity of the
application by loggin in with privileges of a final user, so they can see if
it works fine.

At the end of the development, the access to the base must be revoqued to
the developers

Eventually, the number of users can raise, so they must use the tables,
views, stored procedures, etc. without intervention of the developent team.

The access to the sql server must be "Windows Authentication"

How to resolve this?
How do the queries should look like? (select * from XXXX.myTable)
Do they can be written without the XXXX prefix?
What is the order of creation for: the base, the schema, the login, the
users, etc.?

Could you answer with a step by step script?
Please include a sample of the sql code involved

Thanks in advance

Robeito
First of all what vesrion of SQL Server are you using? If it is SQL Server
2005 you can schema object to separate different groups and give them
permission only for schema that belongs to them. It is very huge and
important topic, I'd suggest to spend a few days to study it

---Schemas and metadata permissions
USE dbDemo
GO
-- Create a dbo table
CREATE TABLE dbo.t (id int)
GO
-- new user
CREATE LOGIN Joe WITH password='jghghghj'
CREATE USER Joe FOR LOGIN Joe
GRANT CREATE TABLE TO Joe
GO
-- this fails, because Joe does not have permissions on schema 'dbo'
SETUSER 'Joe'
GO
CREATE TABLE t1 (id int)
GO
-- schema for Joe
SETUSER
GO
CREATE SCHEMA JoeSchema AUTHORIZATION Joe
GO
-- this still fails, because Joe does not have permissions on schema 'dbo'
SETUSER 'Joe'
GO
CREATE TABLE t1 (id int)
GO
-- this works
CREATE TABLE JoeSchema.t1 (id int)
GO
SETUSER
GO

--- Execution context

-- prepare
CREATE LOGIN Joe WITH PASSWORD = 'hj'
CREATE LOGIN Paul WITH PASSWORD = 'fff'
CREATE LOGIN Tom WITH PASSWORD = 'hhhhh'
GO
CREATE USER Joe FOR LOGIN Joe WITH DEFAULT_SCHEMA = Joe
CREATE USER Paul FOR LOGIN Paul WITH DEFAULT_SCHEMA = Paul
CREATE USER Tom FOR LOGIN Tom WITH DEFAULT_SCHEMA = Tom

CREATE SCHEMA JoeSchema AUTHORIZATION Joe
GO
CREATE SCHEMA PaulSchema AUTHORIZATION Paul
GO
CREATE SCHEMA TomSchema AUTHORIZATION Tom
GO
GRANT CREATE TABLE TO Joe, Paul, Tom
GRANT CREATE PROCEDURE TO Joe, Paul, Tom
GO

-- create as Joe
SETUSER 'Joe'
CREATE TABLE JoeSchema.Joetbl(id int)
GO
CREATE PROCEDURE JoeSchema.JoeProcNoDynamic
AS
SELECT * FROM JoeSchema.Joetbl
GO
CREATE PROCEDURE JoeSchema.JoeProcDynamic
AS
EXECUTE ('SELECT * FROM JoeSchema.JoeTab')
GO
GRANT EXECUTE ON JoeSchema.JoeProcNoDynamic TO Paul
GRANT EXECUTE ON JoeSchema.JoeProcDynamic TO Paul
GO
SETUSER
GO

"robeito" wrote in message

> Let me establish the following didactic escenario:
>
> There are 1000 users registered in the active directory , 50 of them are
> going to use an application that is not developed yet. They belong to
> different departments.
>
> Only the people of the TI support department can use the "sa" account of
> the
> sql server.
>
> The group of developers has 3 members. They must be provided with
> privileges for the sql development in the base (create-alter-etc.) for
> tables, views, stored procedures, etc.
>
> The final users must not have privileges to do that, they will only read
> and
> write data in tables, views, and execute stored procedures.
>
> During the development, the developers sould test the functionallity of
> the
> application by loggin in with privileges of a final user, so they can see
> if
> it works fine.
>
> At the end of the development, the access to the base must be revoqued to
> the developers
>
> Eventually, the number of users can raise, so they must use the tables,
> views, stored procedures, etc. without intervention of the developent
> team.
>
> The access to the sql server must be "Windows Authentication"
>
> How to resolve this?
> How do the queries should look like? (select * from XXXX.myTable)
> Do they can be written without the XXXX prefix?
> What is the order of creation for: the base, the schema, the login, the
> users, etc.?
>
> Could you answer with a step by step script?
> Please include a sample of the sql code involved
>
> Thanks in advance