Fullsix Blog

If you are in the hosting business, or if you just create and delete a lot of users from your SQL Server instance, you might have the need to periodically check if there are orphaned logins in your server. These logins don’t have any database user mapped, and if they don’t have any relevant server role assigned they can be safely removed if needed.

During a recent credential rotation maintenance on our hosting servers I intended to list any orphaned logins that could have been left behind for some reason:

USE MASTER;

CREATE TABLE #dbusers (

sid VARBINARY(85))

EXEC sp_MSforeachdb

'insert #dbusers select sid from [?].sys.database_principals where   type != ''R'''

SELECT name

FROM     sys.server_principals

WHERE    sid IN (SELECT sid

FROM   sys.server_principals

WHERE  TYPE != 'R'

AND name NOT LIKE   ('##%##')

AND name NOT LIKE   ('%\%')

AND name NOT LIKE ('NT   %')

EXCEPT

SELECT DISTINCT sid

FROM   #dbusers)

GO

DROP TABLE #dbusers

This script makes the assumption that the logins you are targeting are SQL logins, and not Windows logins (hence the ‘%\%’ term).

Deixo uma pequena nota, no que respeita a transacções em SQL Server. As transacções podem ser “acumuladas” e cada acção de sucesso (commit) apenas afecta a transacção “corrente”. No que respeita a acções de insucesso (rollback), o cenário é difierente, ou seja, qualquer que sejam as transacções que existam, no instante do insucesso, todas irão falhar.

Os diagramas seguintes ilustram o dito acima: (as imagens foram retiradas da internet)

Steps To Resolve Orphaned Users 1. Run the following command for the orphaned user from the preceding step:

Use Northwind go sp_change_users_login ‘update_one’, ‘test’, ‘test’

This relinks the server login “test” with the the Northwind database user “test”. The sp_change_users_login stored procedure can also perform an update of all orphaned users with the “auto_fix” parameter but this is not recommended because SQL Server attempts to match logins and users by name. For most cases this works; however, if the wrong login is associated with a user, a user may have incorrect permissions. 2. After you run the code in the preceding step, the user can access the database. The user may then alter the password with the sp_password stored procedure:

Use master go sp_password NULL, ‘ok’, ‘test’

This stored procedure cannot be used for Microsoft Windows NT security accounts. Users connecting to a SQL Server server through their Windows NT network account are authenticated by Windows NT; therefore, their passwords can only be changed in Windows NT.

Only members of the sysadmin role can change the password for another user’s login.

http://support.microsoft.com/kb/274188/