How to Identify and Fix Orphaned Users in SQL Server

We know about logins and users in SQL Server, to connect to instance and database we have to go through a defined principals. This principal is defined by Microsoft SQL Server to authenticate and verify the login credentials and decides whether to allow access or not based on certain defined criteria. You can query and find the login details in sys.server_principals andsys.syslogins views.

Sometimes when we try to login to newly migrated database or new restored database we get authentication issue. Basically, SQL Server login is mapped to database user and if this mapping is broken or it is not correctly defined as per SQL Server pricipals then login will not be successful for that particular user of database on particular instance and this user is called orphaned user. Also if security identifier is not available for particular database user in server instance, in that case also user becomes orphaned. If you will query sys.server_principals and sys.syslogins you will get some useful information but it is insufficient details for login as login password is not in actual format. So here we are going to find out orphaned users and techniques to map those users with correct security identifier in an instance.

If we refer sys.server_principals and sys.syslogins view it will give you below details.

SELECT name, principal_id, default_database_name, default_language_name, type_desc
FROM sys.server_principals WHERE type_desc = 'SQL_LOGIN'
GO
SELECT loginname, dbname, password, language FROM sys.syslogins
WHERE password IS NOT NULL
GO

How to find Orphaned Users

I have restored my database ‘ClientInfo’ from one server to another server and I am going to check if there is any orphaned user available. We can execute below query on query editor window for particular database which we have restored or attached.

USE ClientInfo
GO
sp_change_users_login 'Report'
GO

So here we have two orphaned users ‘sa’ and ‘Lisa’ and their username and SID is given side by side . We will use this SID to map and fix the orphaned users.

How to Fix Orphaned Users

We will cross check details by following query for Login SID and User SID differences and we will map it by query. As we can observe for login ‘Lisa’ there is a difference in Login SID and User SID.

USE ClientInfo
GO
SELECT name "LoginName", sid "LoginSID" FROM sys.syslogins WHERE name = 'Lisa'
GO
SELECT name "UserName", sid "UserSID" FROM sys.sysusers WHERE name = 'Lisa'
GO

If you will try to login with login ID ‘Lisa’, you will get authentication failure error. So to fix this login we will use below query because SQL Server login name and user name is same so we can map it easily.

USE ClientInfo
GO
EXEC sp_change_users_login 'Auto_Fix', 'Lisa'
GO

Now again we will execute the query to check orphaned user details. So now login ‘Lisa’ is fixed and we can login successfully.

USE ClientInfo
GO
sp_change_users_login 'Report'
GO

Now consider different scenario, if you have restored the database but login is not available in the instance then you have to create the same to map with database user, You can do the same by executing below query.

USE ClientInfo
GO
EXEC sp_change_users_login 'Auto_Fix', 'Lisa', NULL, 'B@6P@$$w0r6'
GO

where Auto_Fix maps the SQL Server login with the records available in the sys.database_principals view.

above query will check user ‘Lisa’ if it doesn’t exist then it will create a new one with password  B@6P@$$w0r6.

If you want to map a database user with new login, you can do the same by following query.

--Create Login
CREATE LOGIN LisaNew WITH PASSWORD = 'N3wP@$$w0r6'
GO
--Map User Lisa to Login LisaNew
USE ClientInfo
GO
EXEC sp_change_users_login 'Update_One', 'Lisa', 'LisaNew'
GO

Here Update_One maps the user to existing SQL Server login. You have to first create the login then map the user to created login.

Kindly note that Microsoft has included sp_change_users_login to deprecated features after SQL Server 2012 and asked to use ALTER USER feature in future development works.

Data/ Entity/ Domain/ Referential/ User-defined integrity

Data/ Entity/ Domain/ Referential/ User-defined integrity

We all talk about the Data, Entity, Domain, Referential andUser-defined integrity of the database. I think that we have a clear vision and idea related to those before implementing.

I know that all the developer has the clear vision of that.

This article is dedicated to all of my readers and especially the students who needs to learn or take a quick review about it.

Data Integrity

Data is stored in the columns of the table objects. Data Integrity validates the data before getting stored in the columns of the table. 

For example, if an employee is entered with an employee_id value of 123, the database should not allow another employee to have an ID with the same value. If you have an employee_rating column intended to have values ranging from 1 to 5, the database should not accept a value of 6. If the table has a dept_id column that stores the department number for the employee, the database should allow only values that are valid for the department numbers in the company.

Two important steps in planning tables are to identify valid values for a column and to decide how to enforce the integrity of the data in the column. SQL Server supports four type of data integrity.

     1.     Entity Integrity

2. Domain Integrity
3. Referential Integrity
4. User-defined Integrity

Entity Integrity

Entity integrity ensures each row in a table is a uniquely identifiable entity. You can apply entity integrity to a table by specifying a PRIMARY KEY constraint. For example, the ProductID column of the Products table is a primary key for the table.

Entity Integrity can be enforced through indexes, UNIQUE constraints and PRIMARY KEY constraints. 

Example

-- Primary Key Constraint

IF OBJECT_ID('emp_Dtls') IS NOT NULL

   BEGIN

      DROP TABLE emp_Dtls

   END

GO

  

CREATE TABLE emp_Dtls

       (empID   INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

        EmpName VARCHAR(50) NOT NULL)

-- Unique Key Constraint

IF OBJECT_ID('emp_Dtls') IS NOT NULL

   BEGIN

      DROP TABLE emp_Dtls

   END

GO

  

CREATE TABLE emp_Dtls

       (empID   INT NOT NULL IDENTITY(1,1) UNIQUE,

        EmpName VARCHAR(50) NOT NULL)

Domain Integrity

Domain integrity ensures the data values inside a database follow defined rules for values, range, and format. A database can enforce these rules using a variety of techniques, including FOREIGN KEY constraints, CHECK constraints, UNIQUE constraints, and DEFAULT constraints.

Example

-- CHECK Constraint

IF OBJECT_ID('emp_Dtls') IS NOT NULL

   BEGIN

      DROP TABLE emp_Dtls

   END

GO

  

CREATE TABLE emp_Dtls

       (empID    INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

        EmpGarde VARCHAR(1) NOT NULL,

        CHECK (EmpGarde IN('A', 'B', 'C')))

-- FOREIGN KEY Constraint

IF OBJECT_ID('emp_Dtls') IS NOT NULL

   BEGIN

      DROP TABLE emp_Dtls

   END

GO

IF OBJECT_ID('emp_Master') IS NOT NULL

   BEGIN

      DROP TABLE emp_Master

   END

GO

CREATE TABLE emp_Master

       (empID    INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

        EmpName  VARCHAR(50) NOT NULL DEFAULT(''))

       

GO

  

CREATE TABLE emp_Dtls

       (empID    INT NOT NULL UNIQUE,

        EmpGarde VARCHAR(1) NOT NULL,

        FOREIGN KEY (empID) REFERENCES emp_Master(empID))

GO

Referential Integrity

Referential integrity ensures the relationships between tables remain preserved as data is inserted, deleted, and modified. We can apply referential integrity using a FOREIGN KEY constraint and CHECK constraints.

The ProductID column of the Order Details table has a foreign key constraint applied referencing the Orders table. The constraint prevents an Order Detail record from using a ProductID that does not exist in the database. Also, we cannot remove a row from the Products table if an order detail references the ProductID of the row.

User-defined Integrity

User-defined integrity allows you to define specific business rules that do not fall into one of the other integrity categories. All of the integrity categories support user-defined integrity (all column- and table-level constraints in CREATE TABLE, stored procedures, and triggers).

Hope you like it.

SQL Table Constraints

Some of my readers mailed me that,

"Why you write simple article in your web. As you your web contains very good stuff… but some tropics are really simple."

It is facts. I have long list of friends from Linked In web. They send me regular mail to write different types of tropics. From them there is a huge number of junior professional and students. I have to serve them all. Students are a very important reader in my article and I think they really need guideline and I can't ignore them. I also take free class for them in weekends and try to share my knowledge with them. I really like it….

In this article I am trying to illustrate some points related to table constraints. It's not only useful for junior professional but also useful for all readers. So let's starts.

Data integrity rules falls into three categories:

1.    Entity

2.    Referential

3.    Domain

Entity integrity

Entity integrity ensures that the each row of the database is uniquely identified. You can specify the PRIMARY KEY constraint in the table.

Referential Integrity

It ensure that the relationship between tables remain preserved as data is INSERTED, DELETED and MODIFIED.

We can ensure the referential integrity by FOREIGN KEY constraints.

Take an example of "Person" and "Orders" table:

The "Persons" table:

P_Id		LastName		FirstName		Address		City
1		Hansen		Ola		Timoteivn 10		Sandnes
2		Svendson		Tove		Borgvn 23		Sandnes
3		Pettersen		Kari		Storgt 20		Stavanger

The "Orders" table:

O_Id		OrderNo		P_Id
1		77895		3
2		44678		3
3		22456		2
4		24562		1

Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.

The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.

The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

The FOREIGN KEY constraint also prevents that invalid data form being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

SQL Statements

CREATETABLE Person

                        ( P_id            int                    NOTNULL     PRIMARYKEY,

                          LastName  Varchar(50)   NOTNULL,

                          FirstName  Varchar(50)   NOTNULL,

                         Address       Varchar(100) NULL,

                         City               Varchar(50)   NULL

                        )

GO

CREATETABLE Orders

                        ( O_Id        int NOTNULLPRIMARYKEY,

                          OrderNo  int NOTNULL,

                          P_Id        int FOREIGNKEY REFERENCES Persons(P_Id)

                        )

If the table objects are already created we can use the Alter statements to set the foreign key.

ALTERTABLE Orders

ADDCONSTRAINT fk_PerOrders

FOREIGNKEY(P_Id)REFERENCES Persons(P_Id)

Also we can use CASCADE DELETE with referential integrity. In the example if we delete a specified person from the person table the corresponding Order will be automatically deleted. 

Example:

ALTERTABLE Orders

ADDCONSTRAINT fk_PerOrders

FOREIGNKEY(P_Id)

REFERENCES Persons(P_Id)ONDELETECASCADE

We can also use CASCADE UPDATE with referential integrity. CASCADE specifies that the column will be updated when the referenced column is updated.

ALTERTABLE Orders

ADDCONSTRAINT fk_PerOrders

FOREIGNKEY(P_Id)

REFERENCES Persons(P_Id)ONUPDATECASCADE

You can check if any foreign key is defined in your database with cascading actions usingsys.foreign_keys

SELECT    name AS [Constraint Name],

                    OBJECT_NAME(referenced_object_id) [Referenced Object],

                    OBJECT_NAME(parent_object_id) [Parent Object],

                    delete_referential_action_desc [ON DELETE],

                    update_referential_action_desc [ON UPDATE]

FROM        sys.foreign_keys

Domain Integrity

Domain integrity ensures that the values inside a database follow defined rules of values, range and format. Database can enforce the rules using

1.    CHECK Constraints

2.    UNIQUE Constraints

3.    DEFAULT Constraints

Check Constraints

Check constraints contain an expression the database will evaluate when we modify or insert a row. If the expression evaluates to false, the database will not save the row. Building a check constraint is similar to building a WHERE clause. You can use many of the same operators (>, <, <=, >=, <>, =) in additional to BETWEEN, IN, LIKE, and NULL. You can also build expressions around AND and OR operators. We can use check constraints to implement business rules, and tighten down the allowed values and formats allowed for a particular column.

Example:

CREATETABLE Products_2

(

    ProductID int        PRIMARYKEY,

    UnitPrice money  CHECK(UnitPrice > 0 AND UnitPrice < 100)

)

OR

ALTERTABLE  Employees_2

ADD  CONSTRAINT CK_HireDate CHECK(hiredate <GETDATE())

The UNIQUE Constraints

The unique constraints determine that the columns values must be unique. Only one NULL is allowed by UNIQUE constraints.

Example:

CREATETABLE Products_2

(

    ProductID        int    PRIMARYKEY,

    ProductName  nvarchar(40)   Constraint IX_ProductName  UNIQUE

)

OR

ALTERTABLE Products_2

ADD  CONSTRAINT  IX_ProductName UNIQUE(ProductName)

Default Constraints

Default constraints apply a value to a column when an INSERT statement does not specify the value for the column. Although default constraints do not enforce a rule like the other constraints we have seen, they do provide the proper values to keep domain integrity intact. A default can assign a constant value, the value of a system function, or NULL to a column. You can use a default on any column except IDENTITY columns and columns of type timestamp.

Example:

CREATETABLE Orders_2

(

    OrderID          int     IDENTITY    NOTNULL,

    EmployeeID   int                         NOTNULL,

    OrderDate     datetime               NULL  DEFAULT(GETDATE()),

    Freight            money                  NULL  DEFAULT(0)CHECK(Freight >= 0),

    ShipAddress  nvarchar(60)       NULL  DEFAULT('NO SHIPPING ADDRESS'),

    EnteredBy      nvarchar(60)        NOTNULL DEFAULT(SUSER_SNAME())

)

Constraint Maintenance

After creating the constraint, if you think that the you don't needed the constraints you can drop the constraints or disable/enable the constraints.

To DROP the constraints

ALTERTABLE Products

DROPCONSTRAINT CK_Products_UnitPrice

Disable/Enable Constraints

ALTERTABLE Products NOCHECKCONSTRAINT  CK_UnitsOnOrder

ALTERTABLE Products NOCHECKCONSTRAINT ALL

ALTERTABLE Products CHECKCONSTRAINT  CK_UnitsOnOrder

ALTERTABLE Products CHECKCONSTRAINT ALL

Hope you like it.