Re: Logins, Users, Roles, Schemas

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@xxxxxxxx> wrote in message
news:%23afucvTzGHA.2208@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxx> wrote in message
news:egfRrmTzGHA.4580@xxxxxxxxxxxxxxxxxxxxxxx
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.developersdex.com ***




.

Relevant Pages

  • Re: Logging in irrespective of database access
    ... Try connecting using Query Analyzer with one of the problem logins and run ... the following in the context of your demodata and accounts databases: ... This will return the security context used to access the database. ... SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Logging in irrespective of database access
    ... Try connecting using Query Analyzer with one of the problem logins and run ... the following in the context of your demodata and accounts databases: ... This will return the security context used to access the database. ... SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: How do determine who is the owner of an object?
    ... ths sys schema was introduced in SQL 2005 - aren't we were talking about SQL 2000 here? ... The TEST database will still to be owned by your Windows account. ... If I logon to my local machine that is running SQL Server 2000 as administrator, and then logon to SQL Server using Windows Authentication I am a member of the sysadmin server role even though I don't have a SQL Server login explicitly created for Administrator. ... logins do not own database objects directly. ...
    (microsoft.public.sqlserver.security)
  • Re: Logins, Users, Roles, Schemas
    ... However, if they 'own' any objects, the deletion will fail. ... How do you delete these from a database? ... the logins from the database? ... Thus, I believe making Users, Roles, Schemas ...
    (microsoft.public.sqlserver.security)
  • Re: Mumps: the IT worlds best kept secret?
    ... No matter if that is enforced by the database software or by convention in ... Generally they do not use SQL as its not an appropriate or ... The reasons for Mumps databases to exist, are exactly what you pointed out ... Intuitively you would expect RDBMSs with strong and rich schemas would ...
    (comp.lang.mumps)