Monday, April 13, 2015

sp_change_users_login

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. 

No comments: