Using (Transact-sql) SQL query analyser 8.00.2039

Objective: To delete/drop the schema on every run of database setup (i mean drop the schema and build it on every run).
I have done the following to accomplish the same ...


Logged in as sa ... i did the following

use master
exec sp_addlogin 'sas'
exec sp_adduser 'sas'
create database ss
use ss
exec sp_grantdbaccess 'sas', 'ss'

i am having issues with the grant command in the above version.
i want to grant create table, create index, create view, alter table, drop view, drop index, drop table to the user sas

When i ran the command

grant create table, create index, create view, alter table, drop view, drop index, drop table to sas



i got
Server: Msg 165, Level 16, State 6, Line 1
Privilege CREATE INDEX may not be granted or revoked.
Server: Msg 165, Level 16, State 1, Line 1
Privilege ALTER TABLE may not be granted or revoked.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'delete'.


If the grant worked, i thought of creating the schema like
create schema epp authorization sas


Then for removing the schema (as there is no drop schema command for the version mentioned above)
i would have done this,

use master
exec sp_revokedbaccess 'sas'
alter database ss set single_user with rollback immediate
drop database ss
exec sp_droplogin 'sas'



I referred the online book but not of much help on this.

Is this is the correct way to drop/delete a schema on every run of database setup

pls. offer your suggestions

Terminology.

When you write schema I will guess that you mean database rather than a security schema within a database.

User.

A user is an object that belongs to a database. In your create code you added the user sas to the master database. If you want to add that user to the ss database you have to create the ss database first, then USE database ss; before you sp_adduser. If you are using SQL Server 2005 use CREATE User not sp_adduser.

To DROP a database you DROP the database. All the objects in the database including its users are dropped with it. The Logins belong to the server and they can be dropped before or after you drop the database though if you drop a Login and dont drop a database that has that Login as a User you orphan the User.

thank you lkh. Your comments took me forward and complete the task as desired. I am using sql server 2000 so sp_adduser was appropriate.

Also figured out that grants to insert delete (read and write ) should be given as
exec sp_addrolemember 'db_datareader', 'sas'
exec sp_addrolemember 'db_datawriter', 'sas'

Do not user "TABLE" in the ALTER statement.