Re: object level permissions being lost when migrating

Just want to confirm that you are talking about SQL Logins and not windows Logins correct? As far as I know the compatibility level should not be an issue one way or the other but exactly how are you looking at the permissions? Are you running a query or using SSMS? Is this possibly an issue with owners or schemas? Did you use anything other than dbo? I have never heard of this happening before but that doesn't mean it can't I suppose.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"DSM_DBA" <dsmdba@xxxxxxxxxxx> wrote in message news:2F9E2F93-70B1-4C2F-897D-6A6743ECAEE8@xxxxxxxxxxxxxxxx
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: SQL CE Synching Problems
    ... Have you granted IUSER_ServerName access to your publication within SQL ... It looks like the permissions problem is getting access to the publication. ... so the issue has to be between the server tools and the publisher. ... > A request to send data to the computer running IIS has failed. ...
    (microsoft.public.sqlserver.ce)
  • Re: SQL CE Synching Problems
    ... install location of SQL CE instead of under Inetpub like I had done before. ... > so the issue has to be between the server tools and the publisher. ... >>I ran the wizard again to check all the permissions and this is what it ... >> A request to send data to the computer running IIS has failed. ...
    (microsoft.public.sqlserver.ce)
  • Re: object level permissions being lost when migrating
    ... I am talking about SQL Logins, ... can go to the object and see that user X has permissions XYZ on that object, ... I then restored the databases and re-synched the users via the ...
    (microsoft.public.sqlserver.security)
  • Re: Sql Server 2005 Dev. Ed. on Windows Server 2003
    ... Check out this KB which is about transferring Logins: http://support.microsoft.com/kb/246133 ... Also, since this is running on a newly installed Windows Server 2003, is ... them from your older SQL Server instance to the newer one. ...
    (microsoft.public.sqlserver.setup)
  • Re: Disable Sysadmin to view metadata in SQL2005
    ... you are looking for a DRM solution for your database. ... Server does not provide such a solution. ... SQL Server Engine ... If the permissions are not granular ...
    (microsoft.public.sqlserver.security)
Loading