Scripting All Logins on a SQL Server Instance

2014-04-14 - General, Queries, Security

Introduction

Yesterday I showed you how to create a SQL login with a hashed password. Today I would like to show you a T-SQL statement that you can use to automatically generate create statements for all logins on the instance.

Scripting All SQL Server Logins

There are many use cases for such a generated script. You can use it for example to copy all logins from a production instance to a development instance. But, before we can generate such a script we need to lay some ground work.

The first question we need to answer is: What type of principals do we need to include? Of the six server principal types knows to SQL Server, three can be used to actually connect to an instance: SQL_LOGIN, WINDOWS_LOGIN and WINDOWS_GROUP. Therefore, it makes sense to include those three.

The next question is what to do with logins that exist already. For logins of type WINDOWS_LOGIN or WINDOWS_GROUP it makes sense to just leave the existing ones untouched. An existing SQL_LOGIN on the other hand we could alter, for example to take the hashed password. But, if the login exists already, something might be using it, so automatically changing the password is probably not a good idea. That means we need a way to check for existence and only create the login (of any type) if it is new. We can use the SUSER_ID() function for that:

IF(SUSER_ID('LoginName') IS NULL)BEGIN ... END;

Finally, we need to think about logins shipped by Microsoft, as we do not want to touch those either. sys.objects has a column is_ms_shipped that provides just that information. Unfortunately, there is no equivalent in sys.server_principals. Therefore we need to filter based on another feature. It seems that Microsoft is following a naming convention, giving all MS shipped logins names that start and end with two # signs. Besides of those logins there is the famous sa login that we should also ignore. Putting that all together leads to a WHERE clause like this:

  WHERE SP.type_desc IN ('SQL_LOGIN','WINDOWS_GROUP','WINDOWS_LOGIN')
    AND SP.name NOT LIKE '##%##' 
    AND SP.name NOT IN ('SA')

sys.server_principals and sys.sql_logins

The actual list of logins is stored in the sys.server_principals catalog view. We also need to join to sys.sql_logins to get the hashed password. Both catalog views contain the principal_id column, so we can use that to join the two together:

FROM sys.server_principals AS SP
LEFT JOIN sys.sql_logins AS SL
  ON SP.principal_id = SL.principal_id

Scripting the Password

For logins of type SQL_LOGIN we need to specify the hashed password, for the other ones we need to add the FROM WINDOWS clause to the CREATE LOGIN statement. The type of login is accessible in the SP.type_desc column that we have used in the WHERE clause already, so we can just use it again in a CASE statement like this:

CASE WHEN SP.type_desc = 'SQL_LOGIN'
     THEN ' WITH PASSWORD = '+CONVERT(NVARCHAR(MAX),SL.password_hash,1)+' HASHED'
     ELSE ' FROM WINDOWS'
END 

The final Query

That are all the important pieces and we are ready to put them all together:

SELECT 'IF(SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL)BEGIN CREATE LOGIN '+QUOTENAME(SP.name)+
       CASE WHEN SP.type_desc = 'SQL_LOGIN'
            THEN ' WITH PASSWORD = '+CONVERT(NVARCHAR(MAX),SL.password_hash,1)+' HASHED'
            ELSE ' FROM WINDOWS'
       END + ';/*'+SP.type_desc+'*/ END;' 
       COLLATE SQL_Latin1_General_CP1_CI_AS
  FROM sys.server_principals AS SP
  LEFT JOIN sys.sql_logins AS SL
    ON SP.principal_id = SL.principal_id
 WHERE SP.type_desc IN ('SQL_LOGIN','WINDOWS_GROUP','WINDOWS_LOGIN')
   AND SP.name NOT LIKE '##%##' 
   AND SP.name NOT IN ('SA');

When you run this query, you will get an output like this one:

scripting all logins

You might have noticed a few minor tweaks that I applied to the statement: I made sure the login name is quoted by using the QUOTENAME function. I also added a comment that informs us about the type of the login. Finally, on some SQL Server instances this statement leads to a collation conflict. To prevent that, I added a COLLATE clause.

Final Thoughts

There are still a few ways to improve this query. For example, server permissions as well as membership in fixed server roles are not scripted by this statement. The statement also ignores the check password and expiration policy settings as well as the default database. However, I am going to leave those additions to be tackled at a later time.

Happy scripting.

Categories: General, Queries, Security
Tags: , , , , ,

6 Responses to Scripting All Logins on a SQL Server Instance

  1. Zamkinos says:

    Thanks a lot this useful script.
    regards.

  2. @sqlity says:

    @Zamkinos, you are welcome.

  3. Syed says:

    @sqlity Good script but when i try on SQL Server 2014 it throws me this error message when i am trying to run on of login create script
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘0xabbe’. please provide me corrected script to my email gotodba@gmail.com
    Thanks,Syed

  4. @sqlity says:

    @Syed , it works fine for me on SQL Server 2014. Did you copy the script correctly?

  5. NewbieDBA says:

    @sqlity
    on 2012, basically same script , for sql acct. when output executed gives, MSG 15156, password too long, any insight ? Thanks

  6. NewbieDBA says:

    @sqlity Found error, quoted password…

Leave a Reply