Migrating SQL user and password scripts in SQL

SQLMigrating SQL users and password scripts in

Author : CC Abba

2014-6-20

in Daily SQL database operation, it is often necessary to migrate the database or reload the server, when some of the previously established Login account, must be re-established, the following can be exported through a script, in the reload or transplant, it is convenient to execute the script generated, no need to manually build one by one. greatly improve operational efficiency.

select Span style= "Font-size:10.0pt;font-family:"courier new";" > ' Create login [' + p name + +

    case when p type in ( ' U ' ' G ' ) then " from Windows ' else " end + ' with '

    case when p type = then ' password = ' + master sys fn_varbintohexstr l password_hash +

            hashed, ' + Span style= "color:red" > ' sid = ' + master Span style= "Color:gray". sys fn_varbintohexstr l sid + ', check_expiration = ' +

    case when l is_expiration_checked > 0 then ' on, ' Span style= "Color:blue" >else ' OFF, ' end Span style= "Color:gray" >+ ' Check_policy = ' +

    case when l is_policy_checked > 0 then ' on, ' Span style= "Color:blue" >else ' OFF, ' end Span style= "Color:gray" >+

Case when L . credential_id > 0 then ' credential = ' + c. name + ', ' Else "' End

Else "'

End +

' default_database = ' + p. Default_database_name +

Case When len(P. Default_language_name ) > 0

Then ', default_language = ' + P . Default_language_name Else "' End

from Span style= "Font-size:10.0pt;font-family:"courier new";" > sys server_principals p

left Span style= "Font-size:10.0pt;font-family:"courier new";" > join sys sql_logins l on p principal_id = l principal_id

left Span style= "Font-size:10.0pt;font-family:"courier new";" > join sys credentials c on l credential_id = c credential_id

where P. type In (' S ',' U ',' G ') and P . name <> ' sa '