There are two main categories of logins in SQL Server. There are WINDOWS logins in which password restrictions are controlled by the Active Directory (AD) policy. Then there are SQL logins in which the database administrator has more control.
Lately, I have been crafting new database servers with the latest hardware to make sure my organization is ready for growth during the next five years. This includes installing the current version of SQL Server and migrating all the applications from the old server to the new server.
In short, transferring logins between two servers is a common task. I am assuming that these users do not have any special server level permissions.
With that said, lets take a look at a TSQL script that can help you transfer WINDOWS users.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
/* Transfer windows logins */ -- Choose master database USE MASTER GO -- Script Windows logins for transfer SELECT 'CREATE LOGIN ' + QUOTENAME(p.name) + ' FROM WINDOWS ' + 'WITH DEFAULT_DATABASE = ' + quotename(p.default_database_Name) + ', DEFAULT_LANGUAGE = ' + quotename(p.default_language_Name) AS TSQL_CMD FROM sys.server_principals AS p Where p.type = 'U' AND p.name NOT LIKE 'NT%' GO |
Basically, we want to script a CREATE LOGIN TSQL command for all WINDOWS users. The [sys].[server_principals] table contains all types of logins. We want to skip all the service related accounts that start with ‘NT%’. Other properties such as default database and default language can be specified.
Just copy the output from the query executed on the source system; paste it in a new query window on the target system; and run it to completion.
Lets take a look at a TSQL script that can help you transfer SQL users.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
/* Transfer SQL Server logins */ -- Choose master database USE MASTER GO -- Script SQL logins for transfer SELECT 'CREATE LOGIN ' + QUOTENAME(l.name) + ' WITH PASSWORD = ' + CONVERT(VARCHAR(max), l.password_hash, 1) + ' HASHED' + ', DEFAULT_DATABASE = ' + quotename(l.default_database_Name) + ', DEFAULT_LANGUAGE = ' + quotename(l.default_language_Name) + ', CHECK_POLICY = ' + CASE l.is_policy_checked WHEN 0 THEN 'OFF' ELSE 'ON' END + ', CHECK_EXPIRATION = ' + CASE l.is_expiration_checked WHEN 0 THEN 'OFF' ELSE 'ON' END + ', SID = ' + CONVERT(VARCHAR(max), l.SID, 1) AS TSQL_CMD FROM sys.sql_logins AS l Where l.name NOT LIKE '##%##' AND l.sid != 0x01; GO |
The query is different from the previous one. First, we are now going against a system table named [sys].[sql_logins]. Again, we are filtering to remove any credential related accounts and the [sa] account.
There are two new parameters CHECK POLICY and CHECK EXPIRATION. They related to whether or not you want the login to use WINDOWS operating system settings.
The most important parameters are PASSWORD and SID. The HASHED command tells the target server that the password is in an encrypted format. It has to be a SQL Server 2000 or greater login. If you want your restored databases to recognize the new SQL Logins, bring over the original SID’s.
The image below shows the output of the query pasted into a new query window on the target server.
In summary, the scripts above can be used to generate CREATE LOGIN commands for a new server. Just copy the output from the query executed on the source system; paste it in a new query window on the target system; and run it to completion.
