Scripting All Logins on a SQL Server Instance

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:

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:

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:

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:

The final Query

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

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

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.