in location

OR

I'm looking for

  • Wholesalers
    • Uncheck All Check All
  • EXPERTISE
    • Expand
    • Expand
    • Expand
    • Uncheck All Check All
  • Switchboard Manufacturers
    • Uncheck All Check All
OR

The best way to see what Clipsal electric products can do for your home is seeing them in action at a Clipsal Powerhouse Display Centre.

Product stock may vary according to location. If you're after a specific product range, please check with your chosen location before visiting.

10 Results
    Load More Load Complete
    Change Location

    How to Find and Fix Orphaned Users in SQL Server

    Issue
    When migrating or moving a database from one SQL Server instance to another SQL Server instance, orphan user(s) could be created.

    Product
    Microsoft SQL Server

    Environment
    SQL Server 2005, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016

    Cause
    When a database is moved or migrated, the database users in the migrated or moved database are not mapped to any
    particular SQL Server login within the new SQL Server instance. A database user for which the corresponding SQL Server
    login is undefined or is incorrectly defined on a server instance cannot log in to the instance. A database user not mapped
    to SQL Server Login is referred to as an Orphan user.

    Resolution

    *Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

    Below are some of the scripts to identify and fix the Orphan user issue.

    Script for finding list of Orphan USERS for a particular database:
    select db_name() as DBName,* from sys.database_principals 
    where sid not in (select sid from master.sys.server_principals)
    AND type_desc != 'DATABASE_ROLE' AND name != 'guest'

    Script for finding list of Orphan USERS from all the databases:
    exec sp_msforeachdb ' use ?
    select db_name() as DBName,* from sys.database_principals 
    where sid not in (select sid from master.sys.server_principals)
    AND type_desc != ''DATABASE_ROLE'' AND name != ''guest'' '
     
    Script to map a particular database USER with a LOGIN:
    ALTER USER userName WITH LOGIN = loginName
     
    Script to generate Alter User script which can be used to map all orphan USERS with LOGINS in a particular database:
    select 'Alter User '  + name + ' WITH LOGIN = ' + name  from sys.database_principals 
    where sid not in (select sid from master.sys.server_principals)
    AND type_desc != 'DATABASE_ROLE' AND name != 'guest'
     
    Note: These Alter commands will fail, if loginname with same name as username is not already present as a LOGIN, so first create the LOGIN if not already present.
     
    Script to generate Alter USER script which can be used to map all orphan users with logins in all databases:
    exec sp_msforeachdb ' use ?
    select ''Alter User ''  + name + '' WITH LOGIN = '' + name  from sys.database_principals 
    where sid not in (select sid from master.sys.server_principals)
    AND type_desc != ''DATABASE_ROLE'' AND name != ''guest'' '

    Note: These Alter commands need to be run on their specific databases and Alter commands can fail, if loginname with same name as username is not already present as a LOGIN, so first create the LOGIN if not already present.
     
    ** see attachment containing the SQL script text

    Didn’t find what you were looking for?

    Try Searching Again View Our Categories

    Need further assistance?

    Our Customer Care department provides total customer service solutions for our residential, industrial and commercial applications.

    Get Assistance