How to solve orphaned users in SQL & How to fix Orphan User in absense of login

Understanding and dealing with orphaned users in a SQL Server database

Refer:

http://www.mssqltips.com/sqlservertip/1590/understanding-and-dealing-with-orphaned-users-in-a-sql-server-database/

Attaching and restoring databases from one server instance to another are common tasks executed by a DBA. After attaching or restoring of a database, previously created and configured logins in that database do not provide access. The most common symptoms of this problem are that the application may face login failed errors or you may get a message like the user already exists in the current database when you try to add the login to the database. This is a common scenario when performing an attach or a restore, so how do you resolve this?

Solution

When a database is moved from one server to another server the login ids stored in the master database do not align with the login ids stored in each user database. As mentioned above some of the errors you may see after attaching or restoring a database include:

Msg 229, Level 14, State 1
%s permission denied on object %.*s, database %.*s, owner %.*s

or

Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.

Without proper understanding and appropriate planning you may face this problem. You could delete and recreate the user, but you will loose all of the configured permissions. So a proper linking mechanism is required, so that permissions are retained.

Some more possible error messages that you may see include

Before getting to the solution for this problem, it would be better to have a glance at the backend issue. SQL Server logins, which are stored in the master database are mapped against individual databases. SQL Server logins access individual databases using a database user that is mapped to the appropriate SQL Server login. There are two exceptions, the guest account and Microsoft Windows group memberships. The SQL Server 2005 logins on a server instance are visible in the sys.server_principals system catalog view and the sys.syslogins view. For SQL Server 2000 you may get SQL Server login information in sysxlogins table.

On the other hand the mapping information to a database user is stored within the database in the system tablesysusers. It includes the name of the database user and the SID of the corresponding SQL Server login. The permissions of this database user are used for authorization purposes in the database.

So we can say that every time we create a SQL Server login, it will be seen in the sys.server_principals system catalog view or sys.syslogins views on SQL Server 2005 or in the sysxlogins table for SQL Server 2000. The entries inthe sysusers table of a database are linked to SQL Server logins shown in the above picture. This link is created through a column named SID.

If we move our database to another SQL Server instance through any process, the new server might or might not have the same logins and the SIDs of these logins would probably be different from the SIDs of these logins in the original server. What this means is that, the sysusers table in the moved database has SIDs that are not matched with the login info in the master database on this new server. Therefore we get orphaned users.

As an example, I have created and configured four users with permissions in the AdventureWorks database. The users are TestUser1, TestUser2, TestUser3 and TestUser4. When I restored the backup of this database onto another SQL Server 2005 instance, although the users were present in the AdventureWorks database and the logins existed on the new server, but none of these logins had access to the newly restored database.

So keeping this scenario in mind, let us run some queries to see the difference between the SQL Server login SIDs (if SQL Server login is present) and database user SID for TestUser3.

--Script to view difference in SID

USE MASTER
GO 
SELECT name as SQLServerLogIn,SID as SQLServerSID FROM sys.syslogins
WHERE [name] = 'TestUser3'
GO

USE AdventureWorks
GO 
SELECT name DataBaseID,SID as DatabaseSID FROM sysusers
WHERE [name] = 'TestUser3'
GO

The result below shows the SID of the SQL Server login and that of the user database id differ and this is what causes the problem.

Now that we have a better understanding of the problem, it is time to get to some useful commands for analysis and a solution.

I have restored the AdventureWorks database from one instance to another with the four above users. Now to analyze how many orphaned users there are in my restored database, I will run the following T-SQL command which produces a listing of all the orphaned users and in our case all four users are orphaned.

--Command to generate list of orphaned users

USE adventureWorks
GO

sp_change_users_login @Action='Report'
GO

Now that we have the list of the orphaned users we can begin to fix the problem. To overcome this problem, you need to link the SIDs of the users (from sysusers) to the SIDs of the valid logins in the master database. The following command remaps the server login account specified by TestUser1 with the database user specified by TestUser1.

--Command to map an orphaned user

USE AdventureWorks
GO

sp_change_users_login @Action='update_one', 
@UserNamePattern='TestUser1', 
@LoginName='TestUser1'
GO

Or if you are sure that SQL Server login name is the same as the orphaned user of database being mapped, then you may use a shorter command such as this for TestUser2..

--Command to map an orphaned user

EXEC sp_change_users_login 'Auto_Fix', 'TestUser2'
GO

Both commands will map the users to the logins and they will be not orphaned any longer.

If a login name does not exists, you would have to create it first before doing the mapping. A quick way to do this is to use the following command which will create the login and then map the login to the user.

--Command to map an orphaned user to a login that is not present but will be created

EXEC sp_change_users_login 'Auto_Fix', 'TestUser3', null,'pwd'
GO

Summarizing the T-SQL Used

In the above process, the stored procedure sp_change_users_login is used. The variable [ @Action ] specifies the exact use of this stored procedure. It accepts a parameter as varchar(10) and can have one of the following values:

• If parameter is Auto_Fix, database user is mapped with same named SQL Server login. It may also create login, if not present.
• If parameter is Report, it lists the orphaned users and their security identifiers (SID).
• If parameter is Update_One, it links the specified database user to an existing SQL Server login.

Some considerations

• sp_change_users_login requires membership in the db_owner fixed database role. Only members of the sysadmin fixed server role can specify the Auto_Fix option.
• When the orphaned user is mapped, the SID in the master database is allotted to orphaned user, so every time a DB is attached or restored the SID will differ between SQL Server login and database user.
• If you have different Server login names mapped to a database user then do not use the command with Auto_Fixfor linking
• A user may become orphaned also if the corresponding SQL Server login is dropped
• Although obvious, but it is good to mention, after re-linking the password of SQL Server login can be used by the database user.

How to fix Orphan User in absense of login

Error
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 131
Terminating this procedure. The Login name ‘DJ’ is absent or invalid
What is Orphaned User?
An Orphaned User in SQL Server is a database user for which a valid SQL Server Login is not available or it is wrongly defined with the different SID in the SQL Server instance, thereby not allowing the user to get connect to the database to perform activities.
Below scenarios are mostly responsible for Orphan Users
1. A SQL Server Login was accidentally dropped
2. A database is restored with a copy of database from another SQL Server Instance
3. SID of the login is different in sys.server_principals and sys.sysusers
Steps to re-produce issue
Step 1: Creating database SQLDBPool

USE [master]
GO
CREATE DATABASE [sqldbpool] ON PRIMARY
( NAME = N’sqldbpool’, FILENAME = N’C:\sqldbpool.mdf’ , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’sqldbpool_log’, FILENAME = N’C:\sqldbpool_log.ldf’ , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
Step 2:
In this example I am creating user DJ using below script. You can also create user from GUI as well
Using GUI

Right Click On Security Node and Select New Login

Using Script

USE [master]
GO
CREATE LOGIN [DJ] WITH PASSWORD=N'pune@123',
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO

Step 3: I am assigning Data Reader and writer permission to Login DJ to database SQLDBPool

USE [sqldbpool]
GO
CREATE USER [DJ] FOR LOGIN [DJ]
GO
USE [sqldbpool]
GO
EXEC sp_addrolemember N'db_datawriter', N'DJ'
GO
USE [sqldbpool]
GO
EXEC sp_addrolemember N'db_denydatareader', N'DJ'
GO

Step 4: Dropping the login DJ and it will make the user DJ as orphan in SQLDBPool database

USE [master]
GO
DROP LOGIN [DJ]
GO

Steps to fix the issue
Step 1: You can use SP_CHANGE_USERS_LOGIN stored procedure to fix the Orphan user issue, for that you need the Database Owner permission on that database.
Syntax
sp_change_users_login [ @Action= ] 'action'
[ , [ @UserNamePattern= ] 'user' ]
[ , [ @LoginName= ] 'login' ]
[ , [ @Password= ] 'password' ]
[;]
Examples

EXEC sp_change_users_login 'Report' --Reports orphaned user
EXEC sp_change_users_login 'Auto_Fix', 'user' -- auto fix when SID mis-match issue
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

The stored procedure SP_CHANGE_USERS_LOGIN accepts arguments AUTO_FIX, REPORT or UPDATE_ONE as @Action parameter.
AUTO_FIX: If this value is used for @Action parameter, it will create a SQL Server Login if it was not present earlier and will synchronize the SQL Server Login with that of the Database User.We have to also provide the password here.
REPORT: It will display the list of all the Orphaned Users along with the SID (Security Identifiers) value within the current database which are not linked to a SQL Server Login.
UPDATE_ONE:will synchronize the specified database user with an existing SQL Server Login.
Step 2: As first step I am checking orphan user using Report parameter, it will show me the SID and orphan user name.

List of Orphaned User

Step 3: In this scenario we have dropped the SQL Login so it is required to create the login. We can create the login using below query specifying password and mapped it to orphan user

EXEC sp_change_users_login 'Auto_Fix', 'DJ', NULL, 'pune@123'

Step 4: Executing again the report query to list out orphan users.
EXEC sp_change_users_login 'Report'
Use below link to list out and fix the Orphan User issue for all the databases.
http://sqldbpool.com/2010/03/27/script-to-fix-and-list-out-orphan-users-on-all-the-databases-on-server/

Query to find SP name and Table name and object name

Query to find SP name and Table name and object name


SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'cid' + '%'



SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'CId' + '%'



SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'CId' + '%'
AND TYPE = 'P'



SELECT table_catalog,table_name,column_name,data_type,column_default
FROM information_schema.columns

WHERE column_name LIKE '%' + 'CId' + '%'

To know SQL Permissions and IDENTITY Tables with row count in SQL

To know SQL Permissions and IDENTITY Tables with row count in SQL

Script:

To know SQL Permissions

SELECT USER_NAME(P.GRANTEE_PRINCIPAL_ID) AS PRINCIPAL_NAME,

DP.PRINCIPAL_ID,

DP.TYPE_DESC AS PRINCIPAL_TYPE_DESC,

P.CLASS_DESC,

OBJECT_NAME(P.MAJOR_ID) AS OBJECT_NAME,

P.PERMISSION_NAME,

P.STATE_DESC AS PERMISSION_STATE_DESC

FROM SYS.DATABASE_PERMISSIONS P

INNER JOIN SYS.DATABASE_PRINCIPALS DP

ON P.GRANTEE_PRINCIPAL_ID = DP.PRINCIPAL_ID

--WHERE P.STATE_DESC = 'DENY'

where OBJECT_NAME(P.MAJOR_ID) like '%SPname%'

To know IDENTITY Tables with row count in SQL


SELECT b.TableName,b.ColumnName,b.OriginalSeed,b.Step,b.LastValue,a.[RowCount] from 

(
SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName],sOBJ.object_id
      , SUM(sPTN.Rows) AS [RowCount]
FROM 
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY 
      sOBJ.schema_id
      , sOBJ.name,sOBJ.object_id

)as a
join
(
SELECT TableName = OBJECT_NAME(OBJECT_ID) ,
       ColumnName = name ,
       OriginalSeed = seed_value ,
       Step = increment_value ,
       LastValue = last_value ,
       IsNotForReplication = is_not_for_replication,object_id
FROM sys.identity_columns
)as b

Command line parameters are invalid in SQL JOBS

Command line parameters are invalid in SQL JOBS

Solution:

Actually Reason behind this error is login information not properly given or not allocate SSIS packages to job.

Once u choose Server Name corrrect and give username and password and select appropriate Package

Then u can get Success while you run job

The server principal "login" is not able to access the database "Database" under the current security context Resolved

The server principal "login" is not able to access the database "Database" under the current security context Resolved:

The Above Error should be get in our SQL server due to following scenarios

If the user have objects like SP,Views,UDF etc to Execute permission in only restricted Database but that SP code include user doesnt have Execute permission database.

Sample.

Below user have only Testdb Execute permission only  like user mapping see below:

but User have dont have permission on FoodManagement db see below

But user code include insert code in foodmanagement db see below

So when user execute SP he will get error like see below:

The server principal "User" is not able to access the database "FoodManagement" under the current security context.

To Avoid/Resolve:

Kindly map the missing FoodManagement db to that user then he can execute that sp without any error

How to assign Database Role/Permission on New Login Creation?

How to assign Database Role/Permission on New Login Creation?

While you create new login in SQL 2005 you should see the following roles available

So first step is right click under security tab on logins like see below

now u can give name and password like see below

and now under user mapping tab kindly assign as u require database and select schema like dbo and choose Important roles like below

db_datareader-------only select values of object like tables,views

db_datawriter--------only can update/modify on any objects  in SQL server      

After successfully created new login user can login now as see below

If user have db_datareader permission then cant able to modify records but can see all tables values

 like see below bu

So If u want user should modify values then u have to assign db_datawriter permission like see below

and now user can able to update/modify records see below

That is it.

Now if user want to Execute SPs he could get following error

to sort out this issue sysadmin role user like DBA shold give execute permission to that SP as see below

Right click the stored procedures tab under programmbility tab and choose properties like see below

and now add button to choose role and assign like see below

Restore Database Error while DB in use or MDF not found Error

Exclusive access could not be obtained because the database is in use  Resolved

Tweet

Sometimes this is a common error message that we encounter, when we try to restore a SQL database, which is being used by other users.

This can occur due to various reasons. But the most common incident is, users not closing the Management Studio’s query window after they have finished the query task.

There are few ways of resolving this and restore the database.

1. Find all the active connections, kill them all and restore the database.
2. Get database to offline (And this will close all the opened connections to this database), bring it back to online and restore the database.

Method 1

Use the following script to find and kill all the opened connections to the database before restoring database.

 Collapse | Copy Code

declare @sql as varchar(20), @spid as int
select @spid = min(spid)  from master..sysprocesses  where dbid = db_id('<database_name>') 
and spid != @@spid    

while (@spid is not null)
begin
    print 'Killing process ' + cast(@spid as varchar) + ' ...'
    set @sql = 'kill ' + cast(@spid as varchar)
    exec (@sql)

    select 
        @spid = min(spid)  
    from 
        master..sysprocesses  
    where 
        dbid = db_id('<database_name>') 
        and spid != @@spid
end 

print 'Process completed...'

Method 2

Use the following code to take database offline and bring back to online so that all the active connections will be closed. And afterwards restore the database.

 Collapse | Copy Code

alter database database_name<br>set offline with rollback immediate
alter database database_name
set online
go

Method 3Setting the database to single user mode and later, setting to multi user will also close all the active connections. And this method is faster than the 'Method 2'.

use master
go
alter database <dbname>
set single_user with rollback immediate
go
alter database <dbname>
set multi_user
go

DB MDF or LDF not found Error:

When You Create new db and choose .bak file of source db and click 

 Restore DB Source db following error came as see below:

step by step see below:

To resolve this error:

Click options tab and click ellipses tab see below and choose already created mdf and ldf via

How to change Diffrent Schema to DBO?

How to change Diffrent Schema to DBO?

                                                              In our DB there are somany diffrent dbo schemas are available for examble

dbo
db_datareader
db_datawriter
usercreatedschema
sales
..................................etc

but we can change one schema to another by two method one SMO(SqlManagementObject) window and T-SQL way.

SMO way:

Just right click on table and click 'MODIFY'

and now press 'F4' (This facititliy available on only SQL 2005)

Now u can choose properties window under schema tab u can choose wht do u want schema here we should choose 'dbo'

So

now accept yes to proceed furthur changes

and now u can see that Sprockets schema is changed as dbo in table

so now u can query using

select * from dbo.NinePron--------valid

select * from Sprockets.NinePron--------not valid bcoz it changed to dbo


Tsql method:

Right click table and script table as  create new window option

USE [testdb]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NineProngs]------here you should change to [dbo].[NineProngs] from
----[Sprockets].[NineProngs]
(
[source] [int] NULL,
[cost] [int] NULL,
[partnumber] [int] NULL
) ON [PRIMARY]

drop table [Sprockets].[NineProngs]-------drop previous schema with table

u can modify and create it new schema and dropped table previous schema table