Scripting Out the Logins, Server Role Assignments, and Server Permissions

By | In SQL Server | October 01st, 2015

Scripting Out the Logins, Server Role Assignments, and Server PermissionsAs we move into new locations such as offices, apartments, or houses, we need to have the new keys given to us. Obviously, we do not want the keys for our old locations to work on the new ones. Unlike relocations in real life, we will want the SQL Servers authenticated logins and Windows authenticated logins to work on the new servers as we migrate the databases. Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. The repetitive task of scripting each one out and re-creating them one by one on the new server and re-adding the logins to the server roles and granting them server level permissions will be a royal pain. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?

Yes! It is and it can be done.

First, let’s review the fundamentals of logins. Access to the server is granted via logins. There are two main categories of logins:

Windows authenticated logins: These logins can either be logins mapped to Windows users or groups. These logins are integrated with the Active Directory. Here’s the example script to create a Windows authenticated login:

SQL Server authenticated logins: These logins are not based on Windows. They are maintained within SQL Server. Here’s the example script to create a SQL Server authenticated login:

Here are the system functions, stored procedures, tables and case expressions that you need to know to get started:

  • SUSER_ID(): Returns the login identification number of the user.
  • CONVERT(): Converts an expression of one data type to another. In this case, we will use this function to convert the SQL Server login password from binary data type to variable-length Unicode string data.
  • QUOTENAME(): Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.
  • SERVER_PRINCIPALS: Contains a row for every server-level principal. It contains the important data columns such as the login name, login type, default database, and default language.
  • SQL_LOGINS: Returns one row for every SQL Server authentication login. It contains the critical database columns such as the password_hash, is_expiration_checked, and is_policy_checked.
  • SERVER_ROLE_MEMBERS: Returns one row for each member of each fixed and user-defined server role. Note that SQL Server 2012 and later versions have the user-defined server roles.
  • SERVER_PERMISSIONS: Returns one row for each server-level permission. It contains important data columns such as state_desc for the description of permission state and permission_name for the permission name.
  • COLLATE: This clause can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast. A collation specifies the rules for proper use of characters for either a language or an alphabet. Here’s we will change the column collation to SQL_Latin1_General_CP1_CI_AS.
  • CASE EXPRESSION: Evaluates a list of conditions and returns one of the possible result expressions. In this case, we will use the search CASE expression.

Before running the script, you need to be aware that some the SQL Server authenticated logins with their password scripted out will exceed the default data column characters limitation of 256. You will need to change your results to display more characters in the query result to prevent the results being truncated. You can change it by going to the “Tools menu > Options > Query Results > SQL Server > Results to Text” to increase the maximum number or characters returned to 8192 – the maximum.

Here’s the script:

After you have migrated all the required logins over to the destination server, you should check for orphaned users – the database users without associated logins. You can resolve them by mapping them to valid logins. You can report and fix them by using the sp_change_users_login stored proc.

JP Chen
Practice Leader of SQL Server Services
JP has over 13 years of experience in the IT industry specializing in web development, data analysis, and database administration. He has worked on supporting enterprise level corporations in the financial, retailing, pharmaceutical, aerospace, fashion, and education industries as a DBA and Tech Lead. He has initiated, designed, and developed comprehensive solutions on database documentation, monitoring and alerting, stabilization, and continuous improvements with performance monitoring and tuning. He enjoys sharing his new findings, knowledge, and practical hands-on experiences on SQL Server via his two blogs: www.handsonsqlserver.com and www.handsontsql.com.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).

15 thoughts on “Scripting Out the Logins, Server Role Assignments, and Server Permissions”
  1. Very nice script, thank you JP.

    As it stands at the moment, there’s a comma missing after the “ON” in line 15:

    +CASE WHEN SL.is_policy_checked = 1 THEN ‘ON’ ELSE ‘OFF,’ END

    Thanks and regards,
    Paul

  2. Hi again JP.
    I believe the following correctly allows for GRANT statements that use WITH GRANT OPTION:

    — Scripting out the Permissions to Be Granted
    SELECT
    CASE WHEN SrvPerm.state_desc ‘GRANT_WITH_GRANT_OPTION’ THEN SrvPerm.state_desc ELSE ‘GRANT’ END
    + ‘ ‘ + SrvPerm.permission_name + ‘ TO [‘ + SP.name + ‘]’
    + CASE WHEN SrvPerm.state_desc ‘GRANT_WITH_GRANT_OPTION’ THEN ” ELSE ‘ WITH GRANT OPTION’ END
    collate database_default
    AS [– Server Level Permissions to Be Granted –]
    FROM sys.server_permissions AS SrvPerm
    JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id
    WHERE SP.type IN ( ‘S’, ‘U’, ‘G’ )
    AND SP.name NOT LIKE ‘##%##’
    AND SP.name NOT LIKE ‘NT AUTHORITY%’
    AND SP.name NOT LIKE ‘NT SERVICE%’
    AND SP.name (‘sa’);

  3. It seems the web reply system has stripped the two character non-equality operator “” from the code I posted above.
    Here’s the same thing using “!=” instead:

    — Scripting out the Permissions to Be Granted
    SELECT
    CASE WHEN SrvPerm.state_desc != ‘GRANT_WITH_GRANT_OPTION’ THEN SrvPerm.state_desc ELSE ‘GRANT’ END
    + ‘ ‘ + SrvPerm.permission_name + ‘ TO [‘ + SP.name + ‘]’
    + CASE WHEN SrvPerm.state_desc != ‘GRANT_WITH_GRANT_OPTION’ THEN ” ELSE ‘ WITH GRANT OPTION’ END
    collate database_default
    AS [– Server Level Permissions to Be Granted –]
    FROM sys.server_permissions AS SrvPerm
    JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id
    WHERE SP.type IN ( ‘S’, ‘U’, ‘G’ )
    AND SP.name NOT LIKE ‘##%##’
    AND SP.name NOT LIKE ‘NT AUTHORITY%’
    AND SP.name NOT LIKE ‘NT SERVICE%’
    AND SP.name (‘sa’);

    1. Thanks Paul for finding the missing comma on line #15 and correction on the “WITH GRANT OPTION” clause . They will be corrected and updated. In case you find other typos in my blogs, please send them my way.

  4. Very nice script JP. Probably you should also add SID to the Create Login script such that when we copy the logins, we don’t need to run the sync again between logins and users.

  5. Great job JP!
    I just added SID parameter to refrain orphaned users after restore DBs.
    Thanx a lot, this is very useful for me!

    Cheers,
    O.

  6. Really nice script, JP! Is there an easy way to also include all of the permissions needed to make all the users completely ready to work on the new system? You’re script ends with just giving the users ‘connect’ permissions.

  7. I would like to convert/migrate SQL Server Authentication logins to Windows Authentication. This is from SQL Server 2005/08 to 2014. Please suggest whether i can save the SID of the SQL Auth login, and create a new domain login using that SID and grant server level permissions to it?

  8. Hey JP,

    Need some assistance as I am currently wants to transfer DB from SQL 2008 to 2008 R2, need to transfer all logins, permissions and roles… I am new to this SQL and need your assistance. Please let me know what steps I have to follow from start till end.

    Thanks

  9. Also line 29 can use the new syntax for SQL2012 and higher:

    ‘ALTER SERVER ROLE [‘ + SR.name + ‘] ADD MEMBER [‘ + SL.name + ‘]

  10. How can I transfer the results to another system to generate? I’m having an issue with the Hash being read by SQL 2012. I ran the script on SQL 2005