September 24, 2002 at 8:16 am
I am in the process of moving DB's, tables and users to SQL2000. The SQL2000 is already up and going and in production. I need to move the DB's from SQL7 to 2000. I have moved the DB's and logins from 7, but the rights on the tables for the users have not moved. How do I do that? Surely, not manually. Or did I miss something along the way.
September 24, 2002 at 8:49 am
How did you move the databases? I'm guessing you must have DTS'd them over, and not used a backup on 7.0 and a restore on 2000.
Also did when move all the logins did you retain the SID's from the 7.0 server? If you did, then the backup and restore will is a good quick way to go to move everything, data, roles, and permissions. When you restore your backup all you database users will have same rights.
Now if you use DTS what sercurity options did you pick?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 24, 2002 at 10:03 am
True, I did use DTS.
Options selected were "Copy Objects", Selected the tables to transfer, Under "Advanced Copy Options", "selected Copy Database users & Database Roles and Copy Object-level permissions"
September 24, 2002 at 10:50 am
Exactly what permissions are missing?
I just ran a test of a simple database, with one table, and one user, where the user was granted select, insert, delete, and update permissions to the table and it came across ok. By looking at all the DTS generated tsql scripts, and profiler I determined that DTS issued the appropriate "grant" statement to give my users the same permissions on 7.0 as 2000.
Are the permissions missing role based?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 24, 2002 at 11:03 am
Hmmm. That is what is missing. All the Select, Insert, Update, Delete permissions. Somewhere along the way, I missed something. Start over? And if so, what are the correct steps? Move the users to the new server, then the DB's or ?? From you other posting, it sounds like I need to do a B/U and Restore. Correct?
I have looked at the TSQL Scripts, but I don't see where the permissions are granted. I see the permissions for login, but no others.
September 24, 2002 at 11:18 am
There seems to be a number of ways to do move databases. From my simple test, everything came across fine, and DTS did it, no script that I could tell was created to issue the grant select, insert.... statement.
The B/U restore option does work, although if your 7.0 and 2000 server are set up with different default character set then you might have a character set issue.
Here is how I have moved database using the B/U and retore option.
1) Create all Logins on new 2000 server with same sids as 7.0. I did this using the script found here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q246133
Note this article also has some references to other ways to move databases.
2) I backed up my 7.0 database to a disk file.
3) copied the backup to my 2000 box
4) Restored the back on 2000 to create the database I was migrating.
Bingo, everything worked. Maybe I was look.
Did you DTS package run OK?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 24, 2002 at 11:57 am
Well, I did the steps outlined in Q246133 for the SID's. But still no luck. Going to try the B/U & Restore and see what happens. I have just misses something here.
September 24, 2002 at 1:38 pm
If you just need the object permissions, try one of these 2 options, both of which can be executed on the source.
1. This is a SQL-DMO script
a. create a job ActiveX Script
b. paste the code below
c. change the server/database/path to reflect your desired values.
Dim oServer''As SQLDMO.SQLServer
Dim oDatabase ''As SQLDMO.Database
Dim oTBLDDL ''As SQLDMO.TableSchema
Set oServer = CreateObject("SQLDmo.SqlServer")
oServer.LoginSecure = True
oServer.Connect "<your servername>"
SET oDatabase = oServer.Databases("<your database name>")
For Each oTBLDDL In oDatabase.Tables
''SQLDMOScript_AppendToFile=256
''SQLDMOScript_Indexes=73736
''SQLDMOScript_OwnerQualify = 262144
''SQLDMOScript_ObjectPermissions=2
''SQLDMOScript_ToFileOnly=64
If oTBLDDL.SystemObject = False Then
oTBLDDL.Script 256 + 262144 + 2 + 64, "<your output path>" & oDatabase.Name & "_PERM.sql",,8388608
End If
Next
''clean up
oServer.DisConnect
Set oServer = Nothing
2. Paste the code below into an ISQL window and execute it. The results can the be executed on the database that is missing permissions. This assumes the logins/users are there.
SELECT "GRANT "+
CASE c.action
WHEN 26 THEN 'REFERENCES'
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 198 THEN 'CREATE TABLE'
WHEN 203 THEN 'CREATE DATABASE'
WHEN 207 THEN 'CREATE VIEW'
WHEN 222 THEN 'CREATE PROCEDURE'
WHEN 224 THEN 'EXECUTE'
WHEN 228 THEN 'BACKUP DATABASE'
WHEN 233 THEN 'CREATE DEFAULT'
WHEN 235 THEN 'BACKUP LOG'
ELSE 'CREATE RULE'
END+" ON "+a.name+" TO "+b.name+char(13)+"GO"
FROM sysobjects a
INNER JOIN sysprotects c
ON a.id = c.id
INNER JOIN sysusers b
ON c.uid = b.uid
WHERE a.type = 'U'
ORDER BY a.name
Be great!
Michael
Be great!
Michael
September 24, 2002 at 2:01 pm
My apologies for jumping the gun. The SQL-DMO script will only script table permissions. Add these pieces to the DIM area for procedures/views:
Dim oPROCDDL ''As SQLDMO.TableSchema
Dim oVIEWDDL ''As SQLDMO.TableSchema
And add these pieces after the table section:
For Each oPROCDDL In oDatabase.StoredProcedures
''SQLDMOScript_AppendToFile=256
''SQLDMOScript_ObjectPermissions=2
''SQLDMOScript_ToFileOnly=64
If oPROCDDL.SystemObject = False Then
oPROCDDL.Script 256 + 2 + 64, "<your output path>" & oDatabase.Name & "PROC_PERM.sql"
End If
Next
For Each oVIEWDDL In oDatabase.views
''SQLDMOScript_AppendToFile=256
''SQLDMOScript_ObjectPermissions=2
''SQLDMOScript_ToFileOnly=64
If oVIEWDDL.SystemObject = False Then
oVIEWDDL.Script 256 + 2 + 64, "<your output path>" & oDatabase.Name & "VIEW_PERM.sql"
End If
Next
Also, the SQL script will only do tables as well. To get procedure/view information, make the following replacement. Replace WHERE a.type = 'U' with WHERE a.type IN ('U','P','V') on the SQL script.
Sorry for the confusion.
Be great!
Michael
Be great!
Michael
September 24, 2002 at 2:18 pm
Okay, Will give it a try. Need to fight a fire at the moment. Still curious as to what I have done on the stupid side.
Thanks
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply