--------------------------
-- voce esta trocando de servidor ou vai atualizar seu sqlserver do
-- 7 para o 2000 ou do 2000 para o 2005 ou do 2005 para o 2008
-- esse script gera uma stored procedure chamanda
-- "sp_help_revlogin"
-- depois rode na tela o comando "exec sp_help_revlogin"
-- salve o result e depois carregue ele no novo sqlserver
-- rode so depois de ter restaurado todos os bancos.
--------------------------
use master
go
if object_id ('sp_hexadecimal') is not null
drop procedure sp_hexadecimal
go
create procedure sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) output
as
declare @charvalue varchar (514)
declare @i int
declare @length int
declare @hexstring char(16)
select @charvalue = '0x'
select @i = 1
select @length = datalength (@binvalue)
select @hexstring = '0123456789abcdef'
while (@i <= @length)
begin
declare @tempint int
declare @firstint int
declare @secondint int
select @tempint = convert(int, substring(@binvalue,@i,1))
select @firstint = floor(@tempint/16)
select @secondint = @tempint - (@firstint*16)
select @charvalue = @charvalue +
substring(@hexstring, @firstint+1, 1) +
substring(@hexstring, @secondint+1, 1)
select @i = @i + 1
end
select @hexvalue = @charvalue
go
if object_id ('sp_help_revlogin') is not null
drop procedure sp_help_revlogin
go
create procedure sp_help_revlogin @login_name sysname = null as
declare @name sysname
declare @type varchar (1)
declare @hasaccess int
declare @denylogin int
declare @is_disabled int
declare @pwd_varbinary varbinary (256)
declare @pwd_string varchar (514)
declare @sid_varbinary varbinary (85)
declare @sid_string varchar (514)
declare @tmpstr varchar (1024)
declare @is_policy_checked varchar (3)
declare @is_expiration_checked varchar (3)
declare @defaultdb sysname
if (@login_name is null)
declare login_curs cursor for
select p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin from
sys.server_principals p left join sys.syslogins l
on ( l.name = p.name ) where p.type in ( 's', 'g', 'u' ) and p.name <> 'sa'
else
declare login_curs cursor for
select p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin from
sys.server_principals p left join sys.syslogins l
on ( l.name = p.name ) where p.type in ( 's', 'g', 'u' ) and p.name = @login_name
open login_curs
fetch next from login_curs into @sid_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
if (@@fetch_status = -1)
begin
print 'no login(s) found.'
close login_curs
deallocate login_curs
return -1
end
set @tmpstr = '/* sp_help_revlogin script '
print @tmpstr
set @tmpstr = '** generated ' + convert (varchar, getdate()) + ' on ' + @@servername + ' */'
print @tmpstr
print ''
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin
print ''
set @tmpstr = '-- login: ' + @name
print @tmpstr
if (@type in ( 'g', 'u'))
begin -- nt authenticated account/group
set @tmpstr = 'create login ' + quotename( @name ) + ' from windows with default_database = [' + @defaultdb + ']'
end
else begin -- sql server authentication
-- obtain password and sid
set @pwd_varbinary = cast( loginproperty( @name, 'passwordhash' ) as varbinary (256) )
exec sp_hexadecimal @pwd_varbinary, @pwd_string out
exec sp_hexadecimal @sid_varbinary,@sid_string out
-- obtain password policy state
select @is_policy_checked = case is_policy_checked when 1 then 'on' when 0 then 'off' else null end from sys.sql_logins where name = @name
select @is_expiration_checked = case is_expiration_checked when 1 then 'on' when 0 then 'off' else null end from sys.sql_logins where name = @name
set @tmpstr = 'create login ' + quotename( @name ) + ' with password = ' + @pwd_string + ' hashed, sid = ' + @sid_string + ', default_database = [' + @defaultdb + ']'
if ( @is_policy_checked is not null )
begin
set @tmpstr = @tmpstr + ', check_policy = ' + @is_policy_checked
end
if ( @is_expiration_checked is not null )
begin
set @tmpstr = @tmpstr + ', check_expiration = ' + @is_expiration_checked
end
end
if (@denylogin = 1)
begin -- login is denied access
set @tmpstr = @tmpstr + '; deny connect sql to ' + quotename( @name )
end
else if (@hasaccess = 0)
begin -- login exists but does not have access
set @tmpstr = @tmpstr + '; revoke connect sql to ' + quotename( @name )
end
if (@is_disabled = 1)
begin -- login is disabled
set @tmpstr = @tmpstr + '; alter login ' + quotename( @name ) + ' disable'
end
print @tmpstr
end
fetch next from login_curs into @sid_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
end
close login_curs
deallocate login_curs
return 0
go
