USER CREATE SCRIPT FROM DATABASE
We need to preserve the users information from a database before refresh/restore task.
Below script will be used to generate a script out the database having all the user information. This script will generate "User Creation script".
It will be conditional script. If user is already existing in the destination or refreshed database then it will skip else it will create the user.
Declare @C1 Cursor
Declare @Uname nvarchar(200)
Declare @Lname nvarchar(200)
Declare @type char(1)
set @C1 = 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 @C1
FETCH NEXT FROM @C1 into @Uname,@Lname,@type
WHILE (@@FETCH_STATUS=0)
Begin
PRINT 'IF NOT EXISTS (Select name from sys.sysusers where name='''+@Uname+''') create User ['+@Uname+']'+' FOR LOGIN ['+@Lname+'];'
If (@type='S')
BEGIN
PRINT 'IF EXISTS (Select name from sys.sysusers where name='''+@Uname+''') exec sp_change_users_login ''update_one'',['+@Uname+'],['+@Lname+'];'
END
FETCH NEXT FROM @C1 into @Uname,@Lname,@type
End
Close @C1
Deallocate @C1
