Javascript required
Skip to content Skip to sidebar Skip to footer
Home / Creating Logins and Users Script in Sql Server Updated FREE

Creating Logins and Users Script in Sql Server Updated FREE

Creating Logins and Users Script in Sql Server

Introduction

A request is received from the development team asking to copy one of the production databases to the DEV SQL Server in society to simulate real time testing scenarios on information technology. One time the production database backup is restored to the DEV SQL Server case, replacing the onetime re-create, the onetime DEV database users volition exist replaced with the live ones. Only, in that location volition be also a need for the former DEV database logins for the DEV site connection.

What needs to be washed to backup these logins, with their permissions, before replacing the old database with the new production 1 in club to restore it over 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 volition piece of 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 demand 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 alive ones (taking into consideration that the users in our DEV site differ from the live ones).

In guild to map the users' permissions, the following choices are available:

  1. Manually map the users one by i, which can be time consuming
  2. Apply SQL to generate these users and permissions from the sometime DEV database before the restoration, and then run the script to map it again after restoring the new database fill-in

The script consists of two parts; the offset one is to list all database users except the congenital-in ones equally the Create user statements: 

            SELECT            'CREATE USER ['            +            Proper noun            +            '] FOR LOGIN ['            +            Name            +            ']'            Every bit            '--Database Users Creation--'            FROM            sys.database_principals            WHERE            Blazon            IN            (            'U'            ,            'S'            )            AND            NAME            Not            IN            (            'dbo'            ,            'guest'            ,            'sys'            ,            'INFORMATION_SCHEMA'            )

The result for this query volition exist like:

C:\Users\hh.workstation\Desktop\ApexSQL_Script\WebPages\Solutions\How to script SQL Server logins and permissions - Solution center_files\word-imagex.png

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

            SELECT            'EXEC sp_AddRoleMember '''            +            DBRole.            Proper 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 2d query will get the beneath:

C:\Users\hh.workstation\Desktop\ApexSQL_Script\WebPages\Solutions\How to script SQL Server logins and permissions - Solution center_files\word-image1x.png

At present, there are the scripts to create each user on the database level, map it to its corresponding login and grant it admission by adding it to the database roles. The final footstep is to use this script to a database after the restoration procedure.

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

Error 15023: User or part '%s' already exists in the current database.

Then, at that place is one of the DEV users who has the same name as a live i with different SID, as it is created in different SQL Servers. In gild to fix this outcome, use the Exec sp_change_users_login as follows: 

            EXEC            sp_change_users_login            'Auto_Fix'            ,            'YourUser'            Get

As it tin can be concluded, this method requires a big endeavour to generate the SQL script, and this will not work in the state of affairs of SQL Server authentication users due to different SID for the same user name generated in unlike SQL Server instances. Likewise, a good background in the system tables and SPs is needed in society to query and execute it to become 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 exist used, a 3rd 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 past 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 blazon of authentication to connect to that SQL Server. If the SQL Server hallmark is selected, the username and password need to be specified. Later selecting a desired SQL Server, click the Connect button:

    Connection to SQL Server in the New project window

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

    Choose a database under the Select database tab of the New project window

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

    Permissions option under the Options tab of the New project window

    If at that place is a need to create the SQL Server level logins for the database users, the Login with user option should exist too checked. In this instance, it's causeless that the logins already exist in the DEV SQL Server, but it'south needed to take backup for the database users before replacing the old DEV database with the new version from the alive server

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

    Initiating the database loading process

  6. The called database will exist loaded; where from the Construction view desired users tin be selected for the scripting procedure. In club to script the users with their role's membership, all users and all roles will be selected in this example:

    A screenshot of a computer screen Description automatically generated

  7. Click the Script button from the Domicile tab in the main awarding window:

    Initiating the scripting process

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

    Scripting mode step of the Script wizard

    Output type step of the Script wizard

  9. In the Dependencies step, click the Next button

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

    SQL script options step of the Script wizard

  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:

    Generated SQL script with users and database roles

    If in that location is a SQL Server hallmark user common betwixt the live and DEV environments, the login with the original SID can be scripted by choosing the Login with users choice from the Options tab as mentioned in the stride 4., and the event will be like:

    Generated SQL script with users and database roles with SQL Server authentication user

Determination

As it tin can be clearly seen, ApexSQL Script is a simple tool that tin help with this use case. It can be used to script database users, with their membership in their corresponding 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 likewise overcome the SQL Server authentication SID differences issue between unlike SQL Server instances past 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

February v, 2016

Creating Logins and Users Script in Sql Server

DOWNLOAD HERE

Source: https://solutioncenter.apexsql.com/how-to-script-sql-server-logins-and-permissions/

Posted by: zabalaandever.blogspot.com