I use these SQL scripts quite a bit - they work on Microsoft SQL2000.
These are mostly to modify users/owners - usually when moving databases from one box to another. Open up the Query Manager - paste these in (make sure you have the correct database selected and run them.
This will change the owner on all tables back to the dbo:
DECLARE @old sysname, @new sysname, @sql varchar(1000)
SELECT
@old = 'username you want to remap'
, @new = 'dbo'
, @sql = '
IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
WHERE
QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
AND TABLE_SCHEMA = ''' + @old + '''
)
EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''
EXECUTE sp_MSforeachtable @sql
Occasionally you will restore a database where the users may not be in sync between SQL Server and the database... Say you restore a database on your local development machine - the database has a user "Joe Smith" but there is no such user on your local machine. If you try to create that user and grant it permission to the database that already has a user by that name - SQL Server will throw an error. Run the following to find these 'orphaned' users - and re-sync them.
To get list of users:
SP_CHANGE_USERS_LOGIN REPORT
To sync users:
SP_CHANGE_USERS_LOGIN 'Update_One', 'joesmith','joesmith'
Comments
Add Your Own Comment