SQL Express 2012 // make fully functional on different pc

,
81

That actually gave me an error as well... although as I was looking at the SQL database it dawned on me that when I attached them using your code it put brackets around my database names. I attempted to just rename the database by taking the brackets out and it works perfect. My question now I guess is how I can edit your code of attaching the databases to not add the [ ] on either side

82

To fix orphan users:

Use {your database here};
Declare @sqlCommand nvarchar(max) = '';

Set @sqlCommand = (

Select 'Execute sp_change_users_login ' + quotename('update_one', char(39)) + ', ' + quotename(dp.name, char(39)) + ', ' + quotename(sp.name, char(39)) + '; '
From sys.database_principals dp
Inner Join sys.server_principals sp On sp.name = dp.name
Where dp.[type] = 'S'
And (dp.[sid] Is Not Null And dp.sid <> 0x0)
And len(dp.[sid]) <= 16
And suser_sname(dp.sid) Is Null
For xml Path (''));

Print @sqlCommand;
Execute sp_ExecuteSql @sqlCommand;
Go

To copy logins (with new password) from one system to another:

:connect {source system here}
:out C:\Temp\LoginSID.sql

:setvar Login {Login to be copied here}
:setvar uniquePassword {Enter the new password for this login here}

Set Nocount On;

Declare @LoginSID varbinary(85)
, @sqlCommand nvarchar(max);

Select @LoginSID = sp.[sid]
From sys.server_principals sp
Where sp.name = '$(Login)';

 If Exists (Select *
              From sys.server_principals sp
             Where sp.name = '$(Login)')

Begin
Set @sqlCommand = '
If Exists (Select *
From sys.server_principals sp
Where sp.name = ' + quotename('$(Login)', char(39)) + ')
Begin
Drop Login ' + quotename('$(Login)') + ';
End
Go

Create Login ' + quotename('$(Login)') + '
With Password = ' + quotename('$(uniquePassword)', char(39)) + '
, SID = ' + convert(varchar(85), @LoginSID, 1) + '
, default_database = [master]
, check_expiration = Off
, check_policy = On;
Go';
End

Select @sqlCommand;
Go

:out stdout
:connect {Destination Server Here}
:r C:\Temp\LoginSID.sql

!!del c:\temp\LoginSID.sql
Go

The above script needs to be run in SQLCMD mode. What this script does is creates a script with the SID from the source system and then executes that script on the destination system. By creating the login this way - you won't have orphaned users when you restore the databases because they will have the same SID.

Note: remove curly braces when putting in the values...

1 Like
83

Actually I've gotten everything working,

@TaraKizer The first attach code you gave me worked perfectly and everything would have been fine except the apostrophe threw me off and the next code you made gave me the brackets.

Everything tested and everything works perfect after attaching. You've been of great help.

LexisNexis provides such a small amount of support on Law Pre-Discovery and SQL when used together, I'm going to make sure I write something to help others in the future and I'll make sure to credit you for it.

Thanks again!

@jeffw8713 Thanks for the code although I didn't need it at this point, your help means alot and I appreciate it!

1 Like
84

Reminds me of this presentation :slightly_smiling:

85

@TLG1989, if you like what someone did click the heart.

1 Like