burmat / nathan burchfield
Search…
security and systems administration
security / hacking
One-Liners and Dirty Scripts
Domain Enumeration + Exploitation
MSFvenom Cheetsheet
OSCP / PWK - Random Tips and Tricks
systems administration
Active Directory Administration
Exchange Administration
System Fixes
Helper Commands
Log Parsing
Team Foundation Server Administration
SQL Server Administration
Powered By GitBook
SQL Server Administration

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): https://github.com/burmat/burmatscripts/blob/master/mssql/export_logins.sql​
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

LIST SERVER LEVEL PERMISSIONS

To get a list of all server-level permissions, you can run the following query to dertmine 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

CHANGE DB OWNER

USE [DB_NAME]
GO
sp_changedbowner 'sa'
GO
systems administration - Previous
Team Foundation Server Administration
Last modified 3yr ago
Copy link
On this page
TRANSFER SQL SERVER USER LOGINS
LIST SERVER LEVEL PERMISSIONS
CHANGE DB OWNER