How to script SQL Server logins and permissions (4214686)

Return

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Title

How to script SQL Server logins and permissions
Resolution
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 instance, replacing the old copy, the old DEV database users will be replaced with the live ones. But we still need the old DEV database logins for the DEV site connection.

What should we do 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, you must have a server level access 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 Server level.

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

In our case, once the production database is restored to our 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, we have the following choices:
1. Manually map the users one by one, which can be time consuming.
2. Use T-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 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 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 you will get the below:

Now you have 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 your database after the restoration process.

If you face the below error 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 sp_change_users_login as follows:
```
EXEC sp_change_users_login 'Auto_Fix'
	,'YourUser'
GO
```
As you can see, this method requires a big effort to generate the T-SQL script, and this will not work in the situation of SQL Authentication users due to different SID for the same user name generated in different SQL instances. Also you need to have a good background in the system tables and SPs in order to query and execute it to get the users information.

To overcome this issue, and generate T-SQL script for all users with their permissions, and generate the SQL Authentication users with its correct SID, we will use ApexSQL Script, a 3^rd party tool to 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, specify the server that hosts the database you manage to script its users and the type of authentication you will use to connect to that SQL server. If you select SQL Server authentication, you need to specify the username and password. Then click Connect.
3. A list of all databases hosted in your server will be viewed in the right result grid. Choose the database that you need to script its users; which is SQLShackDemo in our example.
4. In the Structure tab (see below), choose Permissions to script the users with their permissions.
  You can select Logins with Users if you need to script the creation of the server level logins for the database users. In our example, we assume that the logins already exist in the DEV server, but we need to take backup for the database users before replacing the old DEV database with the new version from the live server.
5. Click Open.
6. The chosen database components will be listed; from where you can select which users you need to script. In order to script the users with their roles membership, we will select all users and all roles in our example here.
7. Click Script from the Home menu.
8. In the Script wizard window, choose T-SQL as the output type and click Next.
9. In the Dependencies window click Next.
10. In the Output file window, from the Action drop down list, choose create and write to file. Then you have the choice to save the script to a specific path or open it in an editor. Choose Open script in editor then click Create.
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 you have a SQL Authentication user common between the live and DEV environments, you can script the login with the original SID by choosing Logins with Users from the structure window, and the result will be like:

Conclusion

As you can clearly see, ApexSQL Script is a simple tool to help us with this use case. It can be used to script database users, with their membership in their respective database roles, in 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 instances by scripting the SQL Authentication user with its SID from the source server.

Useful links
Author: Ahmad Yaseen