Re: object level permissions being lost when migrating

Certainly,
I first transferred the logins from the 2000 server to the 2005 server via
the script to transfer logins provided by Microsoft.
I then restored the databases and re-synched the users via the
sp_change_users_login procedure. At this point, I expect the SIDs to by
matched up and the object level permissions to be as they were on the
databases that reside on the SQL 2000 server ( which I still have and can
compare for reference ).
However, there are a lot of object level permissions set in this application
and I can look at the permissions on SQL 2000 databases and they do not match
that on the SQL 2005 databases. These are all left at 8.0 compatibility
mode. Some of the permissions are there, some role memberships are there,
but all are not. I have reverese engineered the grant statements from 2000
and run them on the 2005 databases as a work around, but I shouldn't have to
do that. I have not had to when transferring to 2000 servers.
Again, I thank you for your help.


"Andrew J. Kelly" wrote:

Can you be more specific as to what the problem is? Did you remap the
Logins to the Users? If so was it successful? What are you using to
determine they are not there?

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"DSM_DBA" <dsmdba@xxxxxxxxxxx> wrote in message
news:82476EC7-319C-4D83-BBD7-F90074EDBB84@xxxxxxxxxxxxxxxx
Thank you Andrew. While I understand that the permissions are at the
Database level, that is why I am not understanding why I have an issue.
I have done the methods you linked to multiple times from 2000 to 2000
with
no issue.
If you have any other ideas, I would appreciate them ( most of the links
actually didnt' work for me, but I've been to those sites and done the
migrating logins process )


"Andrew J. Kelly" wrote:

Object permissions are at the db user level not the Login and are always
carried over with the db. But you may have lost the link between the
login
and the user. Have a look at these:

http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins
and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or
Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers


http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx
Moving
system dbs 2005
http://www.databasejournal.com/features/mssql/article.php/3379901
Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between
Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server
Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery
Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
2000

http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
Roles and Permissions


--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"DSM_DBA" <dsmdba@xxxxxxxxxxx> wrote in message
news:ABFB1431-34F6-4B4D-85A4-A07AB03C391E@xxxxxxxxxxxxxxxx
Hi,
I'm moving databaes from 2000 to 2005 via either detach/attach or
backup/restore and object level permissions being lost.
I had first moved the logins and that seemed to work fine then upon
restoring the databases and leaving them at 8.0 compatibility ( which
is
the
requirement of the application ) the explicit object permissions are
not
restored.
There must be something obvious staring me in the face, but I don't see
it.
Any help or guidance would be appreciated.
Thanks,





.

Relevant Pages

  • Re: Move databases between servers
    ... What I'm trying to do now is to copy master. ... Now I'm reinstalling sql with identical version, and restore all databases ... Since the logins are in master and operators/agents are in msdb why I ... copy all database and log files to new server ...
    (microsoft.public.sqlserver.setup)
  • Re: Restore master?
    ... of the Logins which are at the server level. ... sp_change_users_login for each db to map the Logins to the Users. ... >> Errors After Restoring Dump ... >> Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.setup)
  • db_owners unable to see login list
    ... add new users to their databases, despite the fact that logins exist ... for these users on the server. ... look at the logins, the complete list is not presented to them. ... assign them to server-level security roles, ...
    (comp.databases.ms-sqlserver)
  • Re: Copying Databases From Server to Another
    ... Manager, for SQL Server instances, which takes care to move logins and msdb ... you can perhaps full backup and restore on the new server users databases... ... you could then script the logins out and recreate them on the destination ...
    (microsoft.public.sqlserver.msde)
  • Re: moving server to a new box
    ... for SQL Server ... There are> about 100 user databases with a lot of logins and jobs. ...
    (microsoft.public.sqlserver.server)