| Author |
 Topic  |
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Astralis
Senior Member
USA
1218 Posts |
 Posted - 24 May 2008 : 06:55:57
|
quote:
You need no server roles to access tables, that is determined by the database roles, not the server roles. Even accessing tables, you don't even need db_owner role, you can settle with "lesser" roles.
Okay...I'm understanding this more. I appreciate it. Why do you think I'm getting that permissions error?
quote:
You'd just need to change one of the schemas
Yes, one schema has about 200 tables. The other schema has a little more than that.  The table was designed like this because the host only allowed one database when I setup the account ages ago. Now they allow you to have five databases.< |
Edited by - Astralis on 24 May 2008 06:56:25 |
 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 24 May 2008 : 07:04:29
|
Well I haven't done that before and it would take me a while to write such a script. Probably the easiest way would be to do a database migration consisting of three steps:
1. Generate a script for all your database tables (and other relevant objects), in SQL Server 2000, not including the object owner
2. Use the script in a new database, to completely recreate the table structure. Like this all tables would be created in the same schema.
3. Transfer the data between the databases as explained in several posts regarding Access to SQL Server migration.< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 24 May 2008 : 07:41:01
|
Actually it was easier than I thought. This one will transfer the objects from astralis into dbo. To change the order, just change the values of @startSchema and @endSchema.
Here you go:
DECLARE @tname varchar(100)
DECLARE @v varchar (200)
DECLARE @startSchema varchar(20)
DECLARE @endSchema varchar(20)
SET @startSchema = 'astralis'
SET @endSchema = 'dbo'
DECLARE testCursor CURSOR
FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.tables WHERE TABLE_SCHEMA=@startSchema
OPEN testCursor
FETCH NEXT FROM testCursor INTO @tname
WHILE @@FETCH_STATUS=0
BEGIN
SET @v = 'ALTER SCHEMA ' + @endSchema + ' TRANSFER ' + @startSchema + '.' + @tname
EXEC(@v)
FETCH NEXT FROM testCursor INTO @tname
END
CLOSE testCursor
DEALLOCATE testCursor
< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 24 May 2008 : 09:32:24
|
Ruirib,
Thank you! I'll get back to you later today.< |
|
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 27 May 2008 : 00:57:04
|
Ruirib,
This is brilliant! Thank you so much!
I suspect more people will be running into this as they upgrade to 2005 or more. Wonderful stuff.
I actually found two non-forum tables that had the same name but different schema. It might be worthwhile placing the forum files in its own database? I don't know, though. Maybe there should be a way to for future releases (.net) to explicitly state as a variable what the schema the forum tables use.< |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 27 May 2008 : 03:19:55
|
the forum tables would allways be created in the schema of the user used in Snitz, that is one of the reasons we allways say to use the forum code to create the tables. also if your user is correctly setup as the db owner it should have no issues accessing any schema in the db, astralis or dbo it does not matter.
quote:
Maybe there should be a way to for future releases (.net) to explicitly state as a variable what the schema the forum tables use.
you can do this in the asp version by simply prepending the schema name to the table prefix in config.asp< |
|
|
|
Topic |
|
Topic Locked
.<