Database Reindex

Below is the query to reindex all the tables in a database

DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'base table'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'Reindexing: ' + @TableName
        DBCC DBREINDEX(@TableName,' ',90)
        FETCH NEXT FROM TableCursor INTO @TableName
    END
CLOSE TableCursor
DEALLOCATE TableCursor

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.