burmat / nathan burchfield
Search…
security / hacking
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:
1
EXEC sp_help_revlogin
Copied!
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:
1
select loginname, sid from master.sys.syslogins
Copied!
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:
1
SELECT name, permission_name, principal_id, sid
2
FROM master.sys.server_permissions
3
LEFT JOIN master.sys.server_principals
4
ON grantee_principal_id = principal_id
Copied!
CHANGE DB OWNER
1
USE [DB_NAME]
2
GO
3
sp_changedbowner 'sa'
4
GO
Copied!
Last modified 2yr ago