burmat / nathan burchfield
  • security and systems administration
  • security / hacking
    • Domain Enumeration + Exploitation
      • Command and Control
      • Credential Access
      • Defense Evasion
      • Discovery
      • Execution
      • Impact
      • Lateral Movement
      • Persistence
      • Privilege Escalation
    • Tools and Services
      • Adobe Experience Manager (AEM)
      • amass
      • ike-scan
      • jq
      • Shodan
      • smbmap
      • tmux
      • tshark
      • Voice Over IP (VoIP)
    • One-Liners and Dirty Scripts
    • MSFvenom Cheetsheet
    • Web Application Hacking
      • Cross-Site Scripting (XXS)
      • SQL Injection (SQLi)
    • OSCP / PWK - Random Tips and Tricks
  • systems administration
    • Active Directory Administration
    • Exchange Administration
    • System Fixes
    • Helper Commands
    • Log Parsing
    • SQL Server Administration
    • Windows Terminal Themes
Powered by GitBook
On this page
  • TRANSFER SQL SERVER USER LOGINS
  • List Server Level Permissions
  • Changing a Database Owner
  1. systems administration

SQL Server Administration

PreviousLog ParsingNextWindows Terminal Themes

Last updated 7 months ago

TRANSFER SQL SERVER USER LOGINS

If you restore a database from one server on to a foreign server, you might need a way to bring over the SQL user accounts too. This occurs frequently when you are restoring an application to a test/backup server that does not have the same user accounts configured. You can easily script out the current users by running the following script (originally from Microsoft): And executing the resulting stored procedure:

EXEC sp_help_revlogin

Paste the output of this stored procedure into the destination server to bring the user account in with their original SID's and current passwords. You can run the following query on both servers to compare the SID's if you wish:

SELECT loginname, sid from master.sys.syslogins

Resource:

List Server Level Permissions

To get a list of all server-level permissions, you can run the following query to determine if something was left over or orphaned after user login changes:

SELECT name, permission_name, principal_id, sid
FROM master.sys.server_permissions 
LEFT JOIN master.sys.server_principals 
ON grantee_principal_id = principal_id 

Changing a Database Owner

USE [DB_NAME]
GO
sp_changedbowner 'sa'
GO
https://github.com/burmat/burmatscripts/blob/master/mssql/export_logins.sql
https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server