Need Help with converting - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need Help with converting

Author

Topic

BENISIZ
Starting Member

11 Posts

Posted - 2006-06-12 : 10:04:49

I've got a SQL script that should be modified so it can get executed under MSSQL 2005 as well as MSSQL 2000. The scrpt looks like this:

---------------------------------------------------------------------------------------------------
USE [master]
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
EXEC sp_addlogin N'SSDBUSERNAME', N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
If EXISTS (Select * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
ALTER LOGIN [SSDBUSERNAME] WITH PASSWORD=N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
IF EXISTS (select * from dbo.sysdatabases where name = 'ISIZ')
DROP DATABASE [ISIZ]
GO
USE [SurveyData]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
GO
USE [SurveyManagement]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
-------------------------------------------------------------------

I've got no problem with exeuting it under MSSQL 2005 but it fails on MSSQL 2000, can anybody help me with this issue?!

Thanks,
Ben

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-12 : 10:08:09

There is a lot here that isn't available in v2000 - you will need to have two scripts or at least an if statement to execute different things on the two servers.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

BENISIZ
Starting Member

11 Posts

Posted - 2006-06-12 : 10:14:41

Thanks for your quick reply,

I just need a script that gives me the same result in MSSQL 2000. The rest is not gonna be a problem. I want to give my user SELECT, UPDATE, DELETE and INSERT rights and my user should be able to connect to the database.It would be great if i could change the password of my user in case he/she exists.

BENISIZ
Starting Member

11 Posts

Posted - 2006-06-12 : 11:00:19

Any idea's ?!!!!!!!!!

BENISIZ
Starting Member

11 Posts

Posted - 2006-06-13 : 04:52:41

I'm now in a position where i can make a nwe login accoutn and add it to the database. the only thing i'm having problems with is giving my user SELECT, UPDATE, DELETE permissions on the hole datbase. Every time try to do thsi i get the following error:

Invalid object name 'MyDatabaseName'.

i've tried the following sql statements:

1. GRANT INSERT, UPDATE, SELECT, DELETE on MyDatabaseName TO SSDBUSERNAME

2. GRANT INSERT, UPDATE, SELECT, DELETE on 'MyDatabaseName' TO SSDBUSERNAME

3. GRANT INSERT, UPDATE, SELECT, DELETE on [MyDatabaseName] TO SSDBUSERNAME

Any idea what the problem might be?!

quote:
Originally posted by nr

There is a lot here that isn't available in v2000 - you will need to have two scripts or at least an if statement to execute different things on the two servers.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-13 : 04:57:02

mydatabasename should be a tablename

if you're giving all permissions on the entire database, just add the user as member of db_datawriter and db_datareader roles

sp_addrolemember

--------------------
keeping it simple...

BENISIZ
Starting Member

11 Posts

Posted - 2006-06-13 : 06:25:42

Thanks for the quick reply,

It worked like a charm...

quote:
Originally posted by jen

mydatabasename should be a tablename

if you're giving all permissions on the entire database, just add the user as member of db_datawriter and db_datareader roles

sp_addrolemember

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources