Generate Script Issue

[mbosco]

I have a script that creates database, tables, and stored procedures, populates tables, creates login/user.

The script creates the database, tables, stored procedures, inserts data, and creates login/user. ok no errors.

The login/user acts like it doesn't have permissions.
So I drop the user/login from the database and recreate it manually through the Enterprise Manager now everything works?

----------------------------------------------------

/****** Object: Database Triton Version 1.2 Build 1966 Script Date: 2/8/2002 3:12:31 PM ******/
Print 'Database Triton Version 1.2 Build 1966 Script Date: 2/8/2002 3:12:31 PM'

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Triton')
DROP DATABASE [Triton]
GO

CREATE DATABASE [Triton] ON (NAME = N'Triton_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\Triton_Data.MDF' , SIZE = 5, FILEGROWTH = 10%) LOG ON (NAME = N'Triton_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\Triton_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

/****** Object: Login triton Script Date: 2/8/2002 3:12:31 PM ******/
if not exists (select * from master.dbo.syslogins where loginname = N'triton')
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'Triton', @loginlang = N'us_english'
if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
select @logindb = N'master'
if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
select @loginlang = @@language
exec sp_addlogin N'triton', null, @logindb, @loginlang
END
GO

/****** Object: User dbo Script Date: 2/8/2002 3:12:31 PM ******/
/****** Object: User triton Script Date: 2/8/2002 3:12:31 PM ******/
if not exists (select * from dbo.sysusers where name = N'triton' and uid < 16382)
EXEC sp_grantdbaccess N'triton', N'triton'
GO

/****** Object: User triton Script Date: 2/8/2002 3:12:31 PM ******/
exec sp_addrolemember N'db_owner', N'triton'
GO


/****** CREATE TABLES
/****** CREATE STORED PROCEDURES
/****** INSERT DEFAULT DATA

 

[Andel]

Have you included (checked) the &quot;Script Object-Level Permisions&quot; in your script?

Andel
andel@barroga.net

http://andyb.8m.com

 

[mbosco]

Sorry Andy,

I had to go onto another problem and forgot about my post.

Not sure about &quot;Script Object-Level Permisions&quot; in script
How do I set permissions for each object that is created.
I thought by creating the login and running these stored procedures, it would set all the permmisions for that database.

EXEC sp_grantdbaccess N'triton', N'triton'
EXEC sp_addrolemember N'db_owner', N'triton'

 

[Andel]

You can use GRANT...

GRANT SELECT, UPDATE ON tablename TO username
GO

I thought you're generating script that's why I ask &quot;Script Object-Level Permisions&quot;-- sorry.

Andel
andel@barroga.net

http://andyb.8m.com