How to script SQL Server logins and permissions

Introduction

A request is received from the development team asking to copy one of the production databases to the DEV SQL Server in order to simulate real time testing scenarios on it. Once the production database backup is restored to the DEV SQL Server instance, replacing the old copy, the old DEV database users will be replaced with the live ones. But, there will be also a need for the old DEV database logins for the DEV site connection.

What needs to be done to backup these logins, with their permissions, before replacing the old database with the new production one in order to restore it again?

Users and permissions scripting methods

To be able to connect to SQL Server, a server level access is needed via SQL Server authenticated or Windows authenticated logins. The database user maps to an existing login by the SID property of the logins and the users. This mapping will work if their SID values are identical. An orphaned user is a user without a corresponding SQL login. Contained databases are an exception, where users are able to connect to the database without authenticating a login at the SQL Server level.

SQL Server logins and permissions are stored in the security catalog system tables in the master database. In order to list these logins with its assigned roles, these system tables need to be queried.

In this case, once the production database is restored to the DEV SQL Server, the old database users already exist as server logins but are not mapped to the database as it is replaced by the live ones (taking into consideration that the users in our DEV site differ from the live ones).

In order to map the users’ permissions, the following choices are available:

1. Manually map the users one by one, which can be time consuming
   
2. Use SQL to generate these users and permissions from the old DEV database before the restoration, then run the script to map it again after restoring the new database backup

The script consists of two parts; the first one is to list all database users except the built-in ones as the Create user statements:

SELECT 'CREATE USER [' + NAME + '] FOR LOGIN [' + NAME + ']' AS '--Database Users Creation--'
FROM sys.database_principals
WHERE Type IN (
		'U'
		,'S'
		)
	AND NAME NOT IN (
		'dbo'
		,'guest'
		,'sys'
		,'INFORMATION_SCHEMA'
		)

The result for this query will be like:

The second step is to generate the database roles membership for each user as the Exec sp_AddRoleMember statement:

SELECT 'EXEC sp_AddRoleMember ''' + DBRole.NAME + ''', ''' + DBUser.NAME + '''' AS '--Add Users to Database Roles--'
FROM sys.database_principals DBUser
INNER JOIN sys.database_role_members DBM ON DBM.member_principal_id = DBUser.principal_id
INNER JOIN sys.database_principals DBRole ON DBRole.principal_id = DBM.role_principal_id

Running the second query will get the below:

Now, there are the scripts to create each user on the database level, map it to its corresponding login and grant it access by adding it to the database roles. The last step is to apply this script to a database after the restoration process.

If the below error is encountered while mapping the users’ access:

Error 15023: User or role ‘%s’ already exists in the current database.

Then, there is one of the DEV users who has the same name as a live one with different SID, as it is created in different SQL Servers. In order to fix this issue, use the Exec sp_change_users_login as follows:

EXEC sp_change_users_login 'Auto_Fix'
	,'YourUser'
GO

As it can be concluded, this method requires a big effort to generate the SQL script, and this will not work in the situation of SQL Server authentication users due to different SID for the same user name generated in different SQL Server instances. Also, a good background in the system tables and SPs is needed in order to query and execute it to get the users information.

To overcome this issue, and generate SQL script for all users with their permissions, and generate the SQL Server authentication users with its correct SID, ApexSQL Script will be used, a 3^rd party tool that can script database objects and data from ApexSQL.

Using ApexSQL Script

ApexSQL Script can be easily used to script the database users with the permissions by following these steps:

1. Start ApexSQL Script
   

2. In the Select databases tab of the New project window, specify the SQL Server that hosts the desired database to manage in order to script its users and the type of authentication to connect to that SQL Server. If the SQL Server authentication is selected, the username and password need to be specified. After selecting a desired SQL Server, click the Connect button:

   

3. A list of all databases hosted on the previously SQL Server will be viewed in the right result grid. Choose the desired database to script its users; which is AdventureWorks2017 in this example:

   

4. Under the Options tab (see below), choose Permissions to script the users with their permissions:

   

   If there is a need to create the SQL Server level logins for the database users, the Login with user option should be also checked. In this example, it’s assumed that the logins already exist in the DEV SQL Server, but it’s needed to take backup for the database users before replacing the old DEV database with the new version from the live server

5. Once everything is set, click the Load button in the bottom-right corner of the New project window:

   

6. The chosen database will be loaded; where from the Structure view desired users can be selected for the scripting process. In order to script the users with their role’s membership, all users and all roles will be selected in this example:

   

7. Click the Script button from the Home tab in the main application window:

   

8. In the Script wizard window, choose Structure as the scripting mode and SQL script as the output type:

   

   

9. In the Dependencies step, click the Next button

10. In the SQL script options step there is an option to choose between to save the script to a specific path or open it in an editor. In this example, the Open script in an editor option is selected and then the Create button is clicked:

    

11. The result script contains statements to create the selected users and add these users to the database roles that specify their permissions in the database, which can be easily executed:

    

    If there is a SQL Server authentication user common between the live and DEV environments, the login with the original SID can be scripted by choosing the Login with users option from the Options tab as mentioned in the step 4., and the result will be like:

    

Conclusion

As it can be clearly seen, ApexSQL Script is a simple tool that can help with this use case. It can be used to script database users, with their membership in their respective database roles, in a few steps without the need to be familiar with the system database tables and SPs and the way to call it. It can also overcome the SQL Server authentication SID differences issue between different SQL Server instances by scripting the SQL Server authentication user with its SID from the source server.

Useful links

• How to script encrypted SQL database objects
• Transfer SQL logins for users with a large number of SQL-authenticated logins
• Script a database for specific DML records only

Author: Ahmad Yaseen