- To reset the identity field on a table:
dbcc checkident (TABLENAME, reseed, 1)
- To dump an SQL file from a database and its contents, use the Database Publishing Wizard
- If you restore backed up database files from one server to another, you’ll end up with orphan users that existed on the old server but not on the new server. You can’t just remove these users from databases through SQL Server Management Studio as normal; you need to run the following command:
exec sp_revokedbaccess 'ORPHANUSERNAME'
- To sync up the existing logins on a server with the logins associated with a database that has been moved/restored on that server, use the following SQL:
EXEC sp_change_users_login 'Update_One', 'username', 'username'
- To get a list of all users in a particular database role:
FROM sysusers AS grp, sysmembers AS membership, sysusers AS member
WHERE grp.sid IS NULL
ORDER BY member.name;