Re: object level permissions being lost when migrating

I am talking about SQL Logins, yes. I agree the compatibility level
shouldn't be an issue, but I have to wonder if it somehow is, because while I
can go to the object and see that user X has permissions XYZ on that object,
when I go to the user, that object is not listed in the securables. We've
found the permissions issues because the application was giving errors and I
could see the permission denied through a trace.
Most objects ( inlcuding those that seem to have lost permissions ) are
owned by dbo. I'm not sure what you're referring to in running a query vs
SSMS-- do you mean when we get the errors?
I've worked around the issue, and I'll have to go through the process again
in another environement once they've tested this one, but it just seems like
this shouldn't happen and I'd like to determine why it did.
Thanks again.

"Andrew J. Kelly" wrote:

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: 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? ... I first transferred the logins from the 2000 server to the 2005 server via ...
    (microsoft.public.sqlserver.security)
  • Re: one SQL DB getting info from another - user setup?
    ... It appears from your narrative that the Main and DB1 databases ... EXEC sp_helpdb 'Main' ... SQL 2000 SP3 and is turned off by default. ... > In the permissions for Main, USER1 and USER2 are listed, but have NO ...
    (microsoft.public.sqlserver.security)
  • Re: Getting SQL7 to work -> SBS2003 Standard vs Premium
    ... > By unstalling SQL7, would it affect the logins and permissions for ... It would affect the logins, but not the permissions directly. ... with to re-associate logins to their users, but it's all covered in SQL ...
    (microsoft.public.windows.server.sbs)
  • Re: very odd user permissions problem from a restore
    ... unless I give them dbo permissions. ... Mgmt Studio does not check which databases you have access to, ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.security)
  • Re: Controlling Access to data from multiple databases
    ... this doesn't work since within the body of the stored procedure it reaches ... procedure fails on permissions. ... read only permission to all databases my stored procedure depends on. ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.security)