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:
| 1 2 | CREATE USER [Teste] GO |
Msg 15007, Level 16, State 1, Line 1
'Test' is not a valid login or you do not have permission.
Now let's create it right:
| 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:
Now what happens to the user if we delete their login?
| 1 2 3 | USE [master] DROP LOGIN [Usuario_Teste] GO |
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 it is a “useless” user in the bank, because without login, it is not possible to connect to the bank.
How to identify orphaned users of a database?
| 1 2 | USE [Testes] EXEC sp_change_users_login 'Report' |
Or using sys.syslogins (SQL Server 2000 onwards):
| 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):
| 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 |
I already recreated the login. How do I recreate the Login x User association?
| 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 |
If successful, SQL Server will return the following messages and by running the EXEC sp_change_users_login 'Report' command again, this user will no longer appear in the returned rows:
The row for user 'Orphan_User' will be fixed by updating your 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.
Recreating the login of all users, all databases, your instance
| 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?
| 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.
| 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 'Orphan_User' 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:
| 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:
| 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!
