Logins, Users, Roles, Schemas

Hi everyone,
I have just migrating my database From SQL2K to SQL2005. Users, Roles
and Schemas were all migrated. No issues there. Logins, on the other
hand, did not get migrated. Thus, I believe making Users, Roles, Schemas
orphans, correct?
If I re-create the login and assigning the login to the database, it
will give an error saying that the user already exists. So, I delete the
User, then it prompts me that I have a schema associated with it, so I
delete the schema, then create the user. Roles have not been touched.
Must I also update the roles? and I'm not sure what schemas are in
comparison to SQL 2000.
Please advise of a better way to deal with users, logins, roles and
schemas.
Thanks,
Audrey
*** Sent via Developersdex http://www.codecomments.com ***Perhaps these resources will help you.
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
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Audrey Ng" <odd26uk@.yahoo.co.uk> wrote in message
news:egfRrmTzGHA.4580@.TK2MSFTNGP05.phx.gbl...
> Hi everyone,
> I have just migrating my database From SQL2K to SQL2005. Users, Roles
> and Schemas were all migrated. No issues there. Logins, on the other
> hand, did not get migrated. Thus, I believe making Users, Roles, Schemas
> orphans, correct?
> If I re-create the login and assigning the login to the database, it
> will give an error saying that the user already exists. So, I delete the
> User, then it prompts me that I have a schema associated with it, so I
> delete the schema, then create the user. Roles have not been touched.
> Must I also update the roles? and I'm not sure what schemas are in
> comparison to SQL 2000.
> Please advise of a better way to deal with users, logins, roles and
> schemas.
> Thanks,
> Audrey
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Thanks for these articles. I have a slightly different issue. We moved a SQL
2000 database from server "A" to server "B" and there are now orphaned
users. They are actually old admins, etc., that we don't even want any more.
How do you delete these from a database? Is it as simple as just deleting
the logins from the database?
I haven't found anything on this topic and I would like some assurance that
it should work without croaking the database.
Thanks,
Ray
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23afucvTzGHA.2208@.TK2MSFTNGP03.phx.gbl...
> Perhaps these resources will help you.
> 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
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Audrey Ng" <odd26uk@.yahoo.co.uk> wrote in message
> news:egfRrmTzGHA.4580@.TK2MSFTNGP05.phx.gbl...
>|||Yes, if they are truly 'orphans', you can just delete them.
However, if they 'own' any objects, the deletion will fail. You will then
have to find those objects, and change the ownership to [dbo] before try
ing
the deletion again.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Ray" <no@.repliesplease.com> wrote in message
news:%23EFW%23FczGHA.3704@.TK2MSFTNGP02.phx.gbl...
> Thanks for these articles. I have a slightly different issue. We moved a
> SQL 2000 database from server "A" to server "B" and there are now orphaned
> users. They are actually old admins, etc., that we don't even want any
> more.
> How do you delete these from a database? Is it as simple as just deleting
> the logins from the database?
> I haven't found anything on this topic and I would like some assurance
> that it should work without croaking the database.
> Thanks,
> Ray
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23afucvTzGHA.2208@.TK2MSFTNGP03.phx.gbl...
>|||Do sp_change_users_login 'report' to get a list of orphans before you decide
to do anything.
Also, check out this kb for more info.
http://support.microsoft.com/kb/274188
-oj
"Ray" <no@.repliesplease.com> wrote in message
news:%23EFW%23FczGHA.3704@.TK2MSFTNGP02.phx.gbl...
> Thanks for these articles. I have a slightly different issue. We moved a
> SQL 2000 database from server "A" to server "B" and there are now orphaned
> users. They are actually old admins, etc., that we don't even want any
> more.
> How do you delete these from a database? Is it as simple as just deleting
> the logins from the database?
> I haven't found anything on this topic and I would like some assurance
> that it should work without croaking the database.
> Thanks,
> Ray
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23afucvTzGHA.2208@.TK2MSFTNGP03.phx.gbl...
>|||Thanks to the both of you.
Ray
"oj" <nospam_ojngo@.home.com> wrote in message
news:eSR$4wgzGHA.4648@.TK2MSFTNGP04.phx.gbl...
> Do sp_change_users_login 'report' to get a list of orphans before you
> decide to do anything.
> Also, check out this kb for more info.
> http://support.microsoft.com/kb/274188
>
> --
> -oj
>
> "Ray" <no@.repliesplease.com> wrote in message
> news:%23EFW%23FczGHA.3704@.TK2MSFTNGP02.phx.gbl...
>