Skip to content

Dirceu Resende

DBA SQL Server and BI Analyst (PowerBI, SSAS, SSIS, SSRS)

  • CONSUTING
    • BI Consulting
    • Power BI Consulting
    • SQL Server Consulting
  • File
  • Series
    • Certification
    • Security and Audit
    • Performance tuning
    • What has changed in T-SQL?
    • Data Protection
  • Jobs
  • Data Platform Events
  • About
  • Contact

Other Languages

Subscribe to a blog by email

Enter your email address to subscribe to this blog and receive notifications of new publications by email.

Join 547 other subscribers

Blog Views

1.789.868 views

Categories

  • Apache / .htaccess (9)
  • Database (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Audit (15)
      • Azure (2)
      • CLR (53)
      • Query Development (83)
      • DMVs and Catalog Views (31)
      • Errors (22)
      • Tools (12)
      • Data Formatting and Validation (23)
      • Little Known Features (19)
      • Hacks (17)
      • Easy (30)
      • File Handling (13)
      • Maintenance (80)
      • Monitoring (35)
      • What not to do (7)
      • OLE Automation (19)
      • Performance tuning (22)
      • Python (1)
      • Safety (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft products (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Career and Courses (13)
  • Career, Courses and Certifications (28)
  • Cell Phones (1)
  • Events and Lectures (63)
  • Programme (57)
    • C # (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Uncategorized (10)
  • SEO (4)
  • Virtualization (5)

Microsoft MVP Data Platform

My Certifications

Training

Posts Archive

Recent Posts

  • Black Friday discounts on SQL Server Trainings (Buy my kkkkk course) November 27th, 2020
  • SQL Server - The “new” GREATEST and LEAST functions November 27th, 2020
  • SQL Server - How to know the date of a user's last login November 9th, 2020
  • Azure in Free Practice # 07 - Administering Databases in Azure November 5th, 2020
  • Analysis Services - An error occurred while opening the model on the workspace database. Reason: An unexpected error occurred (file 'tmcachemanager.cpp', function 'TMCacheManager :: CreateEmptyCollectionsForAllParents') November 5th, 2020
  • February 8rd, 2015
  • 0
  • Database Maintenance SQL Server

Identifying and solving orphaned user problems in SQL Server with sp_change_users_login

Views: 3.373
Reading Time: 7 minutes

Hello guys,
All right ?

In this post I will talk a little about a problem that I have seen a lot in the bases I have been consulting, which are the orphaned users in the base.

A little about permissions in SQL Server

In SQL Server, there is the concept of Login, which is the bank instance level user and is also the object that is associated with the password, password expiration and security policies, server level permissions, and so on.

In addition, there is also the concept of user, which is created for each instance database and holds the privileges of the database in question. Thus, a person has a Login in the instance and one or more created users, because for each database, he must have his user (does not have to be the same name) to receive specific permissions in each database (unless Login the person is in some role that gives him / her privileges on all databases, such as the admin role sysadmin).

SQL Server still has the AD group concept, where if you are part of an AD group, and that group is created in SQL Server as a login, you do not need to create the login for each AD group member. , only from the group itself. In this case, all members of this group will have the same instance-level permissions and will be able to connect to the bank using their AD username and password. (If the group has privileges for this)

What are orphan users?

In theory, all users in all databases must reference some login in order to use this login to authenticate to the database. When there is a user who does not have this reference, we call that user an orphan.

One thing that should be very clear when encountering an unlogged SQL Server user is that there is a difference between orphaned and unlogged users:

  • Orphaned user: This SQL user was created by being mapped to a login. For some reason (possibly a bank restore) this user has lost the login association and has been in this user state without login. This type of user can be remapped at login if this association is lost and can be used to log in to SQL Server.
  • User without login: This SQL Server user was created using the “SQL User without login” option and was not associated with any login during creation. In this case, cannot map this user to a login (Unless you delete the user, re-create by associating with a login and reapply the permissions they had), so you cannot log in to SQL Server using that user.

    This scenario is widely used for security reasons, where a user is required to own the objects, but one does not want to be able to use it to log in to the database or make changes to data or structures, as we can read in the Pinal article. Dave this link here.

How does this happen?

SQL Server tries to prevent you from creating a user without specifying their login:

Transact-SQL
1
2
CREATE USER [Teste]
GO

Msg 15007, Level 16, State 1, Line 1
'Teste' is not a valid login or you do not have permission.

Now let's create it right:

Transact-SQL
1
2
3
4
5
6
7
USE [master]
CREATE LOGIN [Usuario_Teste] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
 
USE [Testes]
CREATE USER [Usuario_Orfao] FOR LOGIN [Usuario_Teste] WITH DEFAULT_SCHEMA=[dbo]
GO

Ready. We created our login and our user:

SQL_Server_User_Orfao1

Now what happens to the user if we delete their login?

Transact-SQL
1
2
3
USE [master]
DROP LOGIN [Usuario_Teste]
GO

SQL_Server_User_Orfao2

That is, this way, even if we have our user created, we will not be able to login to the bank, because our login no longer exists. So we have an orphaned user at the base.. And this is a problem, as you are a “useless” user in the bank, because without the login, it is not possible to connect to the bank.

How to identify orphaned users of a database?

Transact-SQL
1
2
USE [Testes]
EXEC sp_change_users_login 'Report'

Or using sys.syslogins (SQL Server 2000 onwards):

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
    A.name AS UserName,
    A.[sid] AS UserSID
FROM
    sys.sysusers A WITH(NOLOCK)
    LEFT JOIN sys.syslogins B WITH(NOLOCK) ON A.[sid] = B.[sid]
WHERE
    A.issqluser = 1
    AND SUSER_NAME(A.[sid]) IS NULL
    AND IS_MEMBER('db_owner') = 1
    AND A.[sid] != 0x00
    AND A.[sid] IS NOT NULL
    AND ( LEN(A.[sid]) <= 16 )
    AND B.[sid] IS NULL
ORDER BY
    A.name

Or using sys.database_principals (SQL Server 2008 onwards):

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
    A.name AS UserName,
    A.[sid] AS UserSID
FROM
    sys.database_principals A WITH(NOLOCK)
    LEFT JOIN sys.sql_logins B WITH(NOLOCK) ON A.[sid] = B.[sid]
    JOIN sys.server_principals C WITH(NOLOCK) ON A.[name] COLLATE SQL_Latin1_General_CP1_CI_AI = C.[name] COLLATE SQL_Latin1_General_CP1_CI_AI
WHERE
    A.principal_id > 4
    AND B.[sid] IS NULL
    AND A.is_fixed_role = 0
    AND C.is_fixed_role = 0
    AND A.name NOT LIKE '##MS_%'
    AND A.[type_desc] = 'SQL_USER'
    AND C.[type_desc] = 'SQL_LOGIN'
    AND A.name NOT IN ('sa')
    AND A.authentication_type <> 0 -- NONE
ORDER BY
    A.name

SQL_Server_User_Orfao3

I already recreated the login. How do I recreate the Login x User association?

Transact-SQL
1
2
3
USE [Testes]
EXEC sp_change_users_login 'Auto_Fix', 'Usuario_Orfao' -- Isso irá associar o Login 'Usuario_Orfao' ao usuário 'Usuario_Orfao'
GO

In case of success, SQL Server will return the messages below and running the EXEC command sp_change_users_login 'Report' again, that user will no longer appear in the returned lines:

The row for user 'Usuario_Orfao' will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.

SQL_Server_User_Orfao4

Recreating the login of all users, all databases, your instance

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- Identificando todos os usuários órfãos da instância
IF (OBJECT_ID('tempdb..#Usuarios_Orfaos') IS NOT NULL) DROP TABLE #Usuarios_Orfaos
CREATE TABLE #Usuarios_Orfaos (
    [Ranking] INT IDENTITY(1,1),
    [Database] sysname,
    [Username] sysname,
    [SID] UNIQUEIDENTIFIER,
    [Command] VARCHAR(MAX)
)
 
INSERT INTO #Usuarios_Orfaos
EXEC master.dbo.sp_MSforeachdb '
SELECT
    ''?'' AS [database],
    A.name,
    A.[sid],
    ''EXEC [?].[sys].[sp_change_users_login] ''''Auto_Fix'''', '''''' + A.name + '''''''' AS command
FROM
    [?].sys.database_principals A WITH(NOLOCK)
    LEFT JOIN [?].sys.sql_logins B WITH(NOLOCK) ON A.[sid] = B.[sid]
    JOIN sys.server_principals C WITH(NOLOCK) ON A.[name] COLLATE SQL_Latin1_General_CP1_CI_AI = C.[name] COLLATE SQL_Latin1_General_CP1_CI_AI
WHERE
    A.principal_id > 4
    AND B.[sid] IS NULL
    AND A.is_fixed_role = 0
    AND C.is_fixed_role = 0
    AND A.name NOT LIKE ''##MS_%''
    AND A.[type_desc] = ''SQL_USER''
    AND C.[type_desc] = ''SQL_LOGIN''
    AND A.name NOT IN (''sa'')
    AND A.authentication_type <> 0 -- NONE
ORDER BY
    A.name'
 
 
-- Exibindo os usuários órfãos da instância
SELECT * FROM #Usuarios_Orfaos
 
 
-- Executando os comandos no banco
DECLARE @Comando VARCHAR(MAX) = ''
 
SELECT @Comando += Command + '; '
FROM #Usuarios_Orfaos
 
EXEC(@Comando)

I already recreated the login, but it does not have the same username. How do I recreate the Login x User association?

Transact-SQL
1
2
3
4
5
6
USE [Testes]
EXEC sp_change_users_login
    'Update_One',
    'Usuario_Orfao',  -- Usuário
    'Usuario_Teste'   -- Login
GO

There is already a user created. I want to associate it with a new login that I will create at runtime.

Transact-SQL
1
2
3
4
5
6
7
USE [Testes]
EXEC sp_change_users_login
    'Auto_Fix',
    'Usuario_Orfao',  -- Usuário
    NULL,             -- Login. Deixar NULL para criar um novo com o mesmo nome do usuário
    '123'             -- Senha do Login que será criado
GO

SQL Server Return:

Barring a conflict, the row for user 'Usuario_Orfao' will be fixed by updating its link to a new login.
The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating users was 1.

An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'

Common error that occurs in SQL Server when you try to AUTO_FIX an orphaned user and that user has already been created with the WITHOUT LOGIN option. In this case, Auto_Fix will not correct the problem and will return this error message:

Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 214
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.

In this case, the solution is to correct the problem manually using the solutions presented above.

The user cannot be remapped to a login. Remapping can only be done for users who were mapped to Windows or SQL logins.

One thing that should be very clear when encountering an unlogged SQL Server user is that there is a difference between orphaned and unlogged users:

  • Orphaned user: This SQL user was created by being mapped to a login. For some reason (possibly a bank restore) this user has lost the login association and has been in this user state without login. This type of user can be remapped at login if this association is lost and can be used to log in to SQL Server.
  • User without login: This SQL Server user was created using the “SQL User without login” option and was not associated with any login during creation. In this case, cannot map this user to a login, and as such, it is not possible to log in to SQL Server using this user. This scenario is widely used for security reasons, where a user is required to own the objects, but you do not want to be able to use it to log in to the database or make changes to data or structures. If you try to map this type of user to a login, you will come across the error message above.

ALTER USER… WITH LOGIN

Another way to fix orphaned users is to use the ALTER USER command:

Transact-SQL
1
ALTER USER [Usuario] WITH LOGIN = [Login]

To identify and correct orphaned instance users in all databases with the ALTER USER command, use the script below:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- Identificando todos os usuários órfãos da instância
IF (OBJECT_ID('tempdb..#Usuarios_Orfaos') IS NOT NULL) DROP TABLE #Usuarios_Orfaos
CREATE TABLE #Usuarios_Orfaos (
    [Ranking] INT IDENTITY(1,1),
    [Database] sysname,
    [Username] sysname,
    [SID] UNIQUEIDENTIFIER,
    [Command] VARCHAR(MAX)
)
 
INSERT INTO #Usuarios_Orfaos
EXEC master.dbo.sp_MSforeachdb '
SELECT
    ''?'' AS [database],
    A.name,
    A.[sid],
    ''USE [?]; ALTER USER ['' + A.[name] + ''] WITH LOGIN = ['' + A.[name] + '']'' AS command
FROM
    [?].sys.database_principals A WITH(NOLOCK)
    LEFT JOIN [?].sys.sql_logins B WITH(NOLOCK) ON A.[sid] = B.[sid]
    JOIN sys.server_principals C WITH(NOLOCK) ON A.[name] COLLATE SQL_Latin1_General_CP1_CI_AI = C.[name] COLLATE SQL_Latin1_General_CP1_CI_AI
WHERE
    A.principal_id > 4
    AND B.[sid] IS NULL
    AND A.is_fixed_role = 0
    AND C.is_fixed_role = 0
    AND A.[type_desc] = ''SQL_USER''
    AND C.[type_desc] = ''SQL_LOGIN''
    AND A.authentication_type <> 0 -- NONE
ORDER BY
    A.name'
 
SELECT * FROM #Usuarios_Orfaos

Result:

Note: I prefer this method because sp_change_users_login is marked deprecated and may be removed in future editions of SQL Server.

Conclusion

Remember that SQL Server has the concept of Access Groups coming from AD. If there is a Login to an AD group in your instance and you need to allow individual database access for certain users, then those users will probably not be associated with Login and are therefore orphaned users.

In this case, these orphaned users have no negative effect on their base, as they normally access the bank through the AD group, and have individual permissions on certain databases.

So be very careful when identifying and removing / correcting orphaned users, as the commands seen above may return false positives for orphaned users in this situation.

That's it,
To the next!

tags: orphansorphansp_change_users_loginsqlsql serverusersusers

You may also like ...

  • SQL Server - How to resolve the Warning Null value is eliminated by an aggregate or other SET operation

  • Checking a user's permissions in SQL Server

  • SQL Server - How to list and eliminate Windows processes using the CLR (C #)

  • Next Similarities and Differences between DELETE, TRUNCATE and DROP TABLE
  • Previous Arithmetic Overflow in SQL Server 2008 sys.syscolumns Catalog View

Leave a Comment Cancel reply

Dirceu Resende © 2021. All Rights Reserved.