SQL Server Notes

  • 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:

    use TABLENAME;
    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:

    USE databasename;
    SELECT member.name
    FROM sysusers AS grp, sysmembers AS membership, sysusers AS member
    WHERE grp.sid IS NULL
    AND grp.name='rolename'
    AND grp.uid=membership.groupuid
    AND member.uid=membership.memberuid
    ORDER BY member.name;