/*
-- SQL Database Export and migration user account and permissions
-- Eg. [sp_help_revlogin]
*/
-- Sp_hexadecimal
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 (256) Output
As
Declare @ charvalue varchar (256)
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
----- Sp_help_revlogin
If object_id ('SP _ help_revlogin ') is not null
Drop procedure sp_help_revlogin
Go
Create procedure [DBO]. [sp_help_revlogin] @ login_name sysname = NULL
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
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
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
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
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