Encountered a strange issue where i was not able to drop a user and was not able to link this orphan user to an id.
Finally i realized that the SID of the user in the database was not matching with the SID of the user in the windows domain. This was realized when i used the alter user command which gave a more meaningful message as compared to sp_change_users_login
ALTER USER [SDCIT\Ali] WITH name=[SDCIT\Ali]
the option then was to delete the existing user in the database. However before i could delete the user i had to transfer all its owned objects to dbo.
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('SDCIT\Ali')
-- now use the names you find from the above query below in place of the SchemaName below
ALTER AUTHORIZATION ON SCHEMA::[SDC\Ali] TO dbo
once the user was dropped i was able to create a new user and link it to the windows domain.
Finally i realized that the SID of the user in the database was not matching with the SID of the user in the windows domain. This was realized when i used the alter user command which gave a more meaningful message as compared to sp_change_users_login
ALTER USER [SDCIT\Ali] WITH name=[SDCIT\Ali]
the option then was to delete the existing user in the database. However before i could delete the user i had to transfer all its owned objects to dbo.
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('SDCIT\Ali')
-- now use the names you find from the above query below in place of the SchemaName below
ALTER AUTHORIZATION ON SCHEMA::[SDC\Ali] TO dbo
once the user was dropped i was able to create a new user and link it to the windows domain.
No comments:
Post a Comment