Create new login with the same SID

Come creare una login SQL Server con lo stesso SID

select 'if not exists (select * from sys.server_principals where name = ''' + p.name + ''') '
			+ char(13) + char(10) + char(9)
				+'create login [' + p.name + '] ' +
	 case when p.type in('U','G') then 'from windows ' else '' end +
	 'with ' +
	 case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash)
		+ ' hashed, ' +
	  'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' +
	 case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end +
	 'check_policy = ' + case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +
	 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 sys.server_principals p
left join sys.sql_logins l
on  p.principal_id = l.principal_id
left join sys.credentials c
on  l.credential_id = c.credential_id
where p.type in('S','U','G')
and  p.name = '<SQLServer_Login>'

• Non male, ma perchè non usare la stored di MS che, oltre al SID, si porta via anche la password di eventuali login SQL?

  Reply

• Fantastic post however , I was wondering if you could write a litte more on this
  topic? I’d be very grateful if you could elaborate a little bit
  further. Kudos!

  Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.