SQL Server - Create User Script From a Database

A DBA require to keep users’ information from a database before refresh/restore task.

The script can be used to generate a script out the database having all the user information. It will generate User Create Script.

Conditional applied in the script, if user already exist in the destination database then it will skip otherwise create.

DECLARE @cur_user CURSOR

DECLARE @ls_user NVARCHAR(200)

DECLARE @ls_name NVARCHAR(200)

DECLARE @lc_type CHAR(1)

SET @cur_user = CURSOR

FOR SELECT NAME,

           Suser_sname(sid) AS LOGIN_NAME,

           type

    FROM   sys.database_principals

    WHERE  type IN ( 'U', 'S', 'G' )

           AND Suser_sname(sid) IS NOT NULL

           AND principal_id > 4

           AND NAME NOT LIKE '#%'

           AND NAME NOT LIKE 'NT AUTHORITY%'

OPEN @cur_user

FETCH NEXT FROM @cur_user INTO @ls_user, @ls_name, @lc_type

WHILE ( @@FETCH_STATUS = 0 )

  BEGIN

      PRINT 'IF NOT EXISTS (Select name from sys.sysusers where name='''

            + @ls_user + ''') create User [' + @ls_user + ']'

            + ' FOR LOGIN [' + @ls_name + '];'

      IF ( @lc_type = 'S' )

        BEGIN

            PRINT 'IF EXISTS (Select name from sys.sysusers where name='''

                  + @ls_user

                  + ''') exec sp_change_users_login ''update_one'',['

                  + @ls_user + '],[' + @ls_name + '];'

        END

      FETCH NEXT FROM @cur_user INTO @ls_user, @ls_name, @lc_type

  END

CLOSE @cur_user

DEALLOCATE @cur_user