for the jobs, if you backup the msdb database and later on restore it to your new server all your jobs will be there.
you can also script them and apply them to the new server.
make sure you script all the logins and permissions first. save the script to apply in the new server.
then you can restore the msdb database with replace.
do this before restoring any user database.
for the logins I use the following procedure:
It also scripts the server permissions and takes special care on system objects like permissions on logins and endpoints.
I will show you how to use it later on in this post:
use master
go
--==================================================================================
-- Create shell.
IF object_id('sp_getthelogins') IS NULL EXEC ('CREATE PROCEDURE sp_getthelogins AS PRINT ''Krishna Krishna''')
go
GO
ALTER PROCEDURE sp_getthelogins
@radhe sysname=null,
@login_name sysname = NULL,
@WITH_PRINT bit = 1
WITH ENCRYPTION
AS
/*
=======================================================================
Script : sp_getthelogins
Author : Marcello Miorelli
Date : 20-dec-2018
Desc : this procedure returns ithe logins and the script as how to create them
Usage :
-- this way below it works, by passing a temp table as a parameter
--===============================================================================================================
if OBJECT_ID('tempdb.dbo.#Jagannatha') IS NOT NULL
DROP TABLE #Jagannatha
CREATE TABLE #Jagannatha(
I INT IDENTITY(1,1) NOT NULL,
Servername SYSNAME COLLATE Latin1_General_CI_AS NOT NULL,
NAME SYSNAME COLLATE Latin1_General_CI_AS NOT NULL,
the_type SYSNAME COLLATE Latin1_General_CI_AS NOT NULL,
script varchar(4000) COLLATE Latin1_General_CI_AS NULL,
PRIMARY KEY CLUSTERED (SERVERNAME,NAME,I)
)
exec sp_getthelogins
@Radhe ='#Jagannatha',
@WITH_PRINT=0
select *
from #Jagannatha
--ORDER BY NAME
--ORDER BY I
Links:
-- How to transfer logins and passwords between instances of SQL Server
-- https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server
-- Scripting Out the Logins, Server Role Assignments, and Server Permissions
-- https://www.datavail.com/blog/scripting-out-the-logins-server-role-assignments-and-server-permissions/
-- [How to script out the grant view on login permission?][1]
-- https://dba.stackexchange.com/a/218273/22336
=======================================================================
History
Date Action User Desc
20-dec-2018 created Marcello Miorelli
23-dec-2018 ammended M.M. only inserts into table @Radhe if it exists, passed as parameter
28-dec-2018 new features M.M. added the server roles and the server permissions for each login
17-Jan-2019 fixes M.M. fixes in the script
09-Feb-2022 ammended M.M. parameter @WITH_PRINT - only prints if that is on
15-Mar-2022 ammended M.M. added the grant on login and grant on endpoint permissions
=======================================================================
*/
--======================================
-- describe primary blocks of processing
-- describe action of logical groups of commANDs
-- describe individual actions within a commAND set
SET NOCOUNT ON
SET DATEFORMAT DMY
SET DEADLOCK_PRIORITY NORMAL;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @log NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @Object_ID INT
DECLARE @DB_ID INT
DECLARE @Object_ID_STR VARCHAR(15)
DECLARE @Object_TYPE VARCHAR(3)
DECLARE @DBName sysname
,@vCrlf CHAR(2);
DECLARE @ERR_SEV SMALLINT,
@ERR_STA SMALLINT
DECLARE @ParamDefinition NVARCHAR(MAX)
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 @script varchar (4000)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
DECLARE @TT SYSNAME -- THE_TYPE
--=========================================================================================================
BEGIN TRY
SELECT @TT = '1 - Login creation'
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FAST_FORWARD 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 OUTER JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' )
AND p.name <> 'sa'
AND P.sid <> 0x01 -- 0x01 is either SA or a login with that position for example dude
ELSE
DECLARE login_curs CURSOR FAST_FORWARD 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 OUTER 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
IF @WITH_PRINT = 1
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @script = '/* sp_getthelogins script '
IF @WITH_PRINT = 1
PRINT @script
SET @script = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
IF @WITH_PRINT = 1
PRINT @script
IF @WITH_PRINT = 1
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
IF @WITH_PRINT = 1
PRINT ''
SET @script = '-- Login: ' + @name
IF @WITH_PRINT = 1
PRINT @script
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @script = CHAR(10) + CHAR(10) +
'IF NOT EXISTS (select * from sys.server_principals SP' + CHAR(10) +
' where SP.name = ' + '''' + @name + '''' + ')' + CHAR(10) +
'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']' + ';' + CHAR(10) + CHAR(10)
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 @script = CHAR(10) + CHAR(10) + CHAR(10) +
'IF NOT EXISTS (select * from sys.server_principals SP ' + CHAR(10) +
' where SP.name = ' + '''' + @name + '''' + ')' + CHAR(10) +
'CREATE LOGIN ' + QUOTENAME( @name ) + CHAR(10) +
' WITH PASSWORD = ' + @PWD_string + ' HASHED,' + CHAR(10) +
' SID = ' + @SID_string + ',' + CHAR(10) +
' DEFAULT_DATABASE = [' + @defaultdb + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @script = @script + ',CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @script = @script + ',CHECK_EXPIRATION = ' + @is_expiration_checked
END
SET @script = @script + ';' + CHAR(10)
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @script = @script + CHAR(10) +
' DENY CONNECT SQL TO ' + QUOTENAME( @name ) + ';'
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @script = @script + CHAR(10) +
' REVOKE CONNECT SQL TO ' + QUOTENAME( @name ) + ';'
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @script = @script + CHAR(10) +
' ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE' + ';' + CHAR(10)
END
if @WITH_PRINT=1
PRINT @script
IF @radhe IS NOT NULL
BEGIN
--=============================================================
-- INSERTS INTO THE @Radhe table - if one was provided
--=============================================================
-- generate the script to load into the table
select @sql = case when @radhe IS not null then
N'
insert into ' + @radhe + N'(Servername,name,the_type,script) values(@@servername,@name,@the_type,@script)'
else N''
end
--set the parameters
SET @ParamDefinition = N'@name sysname,
@the_type sysname,
@script VARCHAR(4000)';
--insert the values into the table
EXEC sp_executesql @SQL, @ParamDefinition, @name=@name, @script=@script, @the_type=@tt
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
begin try
CLOSE login_curs
end try
begin catch
end catch
begin try
DEALLOCATE login_curs
end try
begin catch
end catch
SELECT @TT = '2 - Server Role Membership'
--============================================================================================
-- Scripting Out the Role Membership to Be Added
--============================================================================================
IF @radhe IS NOT NULL
BEGIN
--=============================================================
-- INSERTS INTO THE @Radhe table - if one was provided
--=============================================================
-- generate the script to load into the table
select @sql = case when @radhe IS not null then
N'
insert into ' + @radhe + N'(Servername,name,the_type,script)
SELECT @@servername,
SL.name,
@the_type,' +
'''EXEC master..sp_addsrvrolemember @loginame = N'''''' + SL.name + '''''', @rolename = N'''''' + SR.name + ''''''
''' + ' AS [-- Server Roles the Logins Need to be Added --] ' +
'FROM master.sys.server_role_members SRM
JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN (''S'',''G'',''U'')
--AND SL.name NOT LIKE ''##%##''
--AND SL.name NOT LIKE ''NT AUTHORITY%''
--AND SL.name NOT LIKE ''NT SERVICE%''
AND SL.name <> (''sa'')
AND SL.sid <> 0x01 -- 0x01 is either SA or a login with that position for example dude
'
else N''
end
--set the parameters
SET @ParamDefinition = N'@the_type sysname';
--insert the values into the table
EXEC sp_executesql @SQL, @ParamDefinition, @the_type=@tt
END
SELECT @TT = '3 - Server Permissions to Be Granted'
--============================================================================================
-- -- Scripting out the Permissions to Be Granted
--============================================================================================
IF @radhe IS NOT NULL
BEGIN
--=============================================================
-- INSERTS INTO THE @Radhe table - if one was provided
--=============================================================
-- generate the script to load into the table
select @sql = case when @radhe IS not null then
N'
insert into ' + @radhe + N'(Servername,name,the_type,script)
SELECT
@@servername,
SP.name,
@THE_TYPE,
CASE WHEN SrvPerm.state_desc <> ''GRANT_WITH_GRANT_OPTION''
THEN SrvPerm.state_desc
ELSE ''GRANT''
END
+ '' '' + SrvPerm.permission_name
+ CASE SrvPerm.class_desc WHEN ''SERVER_PRINCIPAL'' THEN
'' ON LOGIN::'' + QUOTENAME(t.name) ELSE '''' END
+ CASE SrvPerm.class_desc WHEN ''ENDPOINT'' THEN
'' ON ENDPOINT::'' + QUOTENAME(e.name) ELSE '''' END
+ '' TO ['' + SP.name + '']'' +
CASE WHEN SrvPerm.state_desc <> ''GRANT_WITH_GRANT_OPTION''
THEN ''''
ELSE '' WITH GRANT OPTION''
END collate database_default AS [-- Server Level Permissions to Be Granted --]
FROM sys.server_permissions AS SrvPerm
INNER JOIN sys.server_principals AS SP
ON SrvPerm.grantee_principal_id = SP.principal_id
LEFT OUTER JOIN sys.server_principals AS t
ON SrvPerm.major_id = t.principal_id
LEFT OUTER JOIN sys.endpoints AS e
ON SrvPerm.major_id = e.endpoint_id
WHERE SP.type IN ( ''S'', ''U'', ''G'' )
AND SP.name NOT LIKE ''##%##''
AND SP.name NOT LIKE ''NT AUTHORITY%''
AND SP.name NOT LIKE ''NT SERVICE%''
AND SP.sid <> 0x01 -- 0x01 is either SA or a login with that position for example dude
'
else N''
end
--set the parameters
SET @ParamDefinition = N'@the_type sysname';
--insert the values into the table
EXEC sp_executesql @SQL, @ParamDefinition, @the_type=@tt
END
RETURN 0
END TRY
BEGIN CATCH
begin try
CLOSE login_curs
end try
begin catch
end catch
begin try
DEALLOCATE login_curs
end try
begin catch
end catch
DECLARE @ERRORMESSAGE NVARCHAR(512),
@ERRORSEVERITY INT,
@ERRORNUMBER INT,
@ERRORSTATE INT,
@ERRORPROCEDURE SYSNAME,
@ERRORLINE INT,
@XASTATE INT
SELECT
@ERRORMESSAGE = ERROR_MESSAGE(),
@ERRORSEVERITY = ERROR_SEVERITY(),
@ERRORNUMBER = ERROR_NUMBER(),
@ERRORSTATE = ERROR_STATE(),
@ERRORPROCEDURE = ERROR_PROCEDURE(),
@ERRORLINE = ERROR_LINE()
SET @ERRORMESSAGE =
(
SELECT CHAR(13) +
'Message:' + SPACE(1) + @ErrorMessage + SPACE(2) + CHAR(13) +
'Error:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorNumber) + SPACE(1) + CHAR(13) +
'Severity:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorSeverity) + SPACE(1) + CHAR(13) +
'State:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorState) + SPACE(1) + CHAR(13) +
'Routine_Name:' + SPACE(1) + COALESCE(@ErrorProcedure,'') + SPACE(1) + CHAR(13) +
'Line:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorLine) + SPACE(1) + CHAR(13) +
'Executed As:' + SPACE(1) + SYSTEM_USER + SPACE(1) + CHAR(13) +
'Database:' + SPACE(1) + DB_NAME() + SPACE(1) + CHAR(13) +
'OSTime:' + SPACE(1) + CONVERT(NVARCHAR(25),CURRENT_TIMESTAMP,121) + CHAR(13)
)
--We can also save the error details to a table for later reference here.
RAISERROR (@ERRORMESSAGE,16,1)
RETURN (-1)
END CATCH;
GO
exec sys.sp_MS_marksystemobject 'sp_getthelogins'
and one way to use it is:
if OBJECT_ID('tempdb.dbo.#Jagannatha') IS NOT NULL
DROP TABLE #Jagannatha
CREATE TABLE #Jagannatha(
I INT IDENTITY(1,1) NOT NULL,
Servername SYSNAME COLLATE Latin1_General_CI_AS NOT NULL,
NAME SYSNAME COLLATE Latin1_General_CI_AS NOT NULL,
the_type SYSNAME COLLATE Latin1_General_CI_AS NOT NULL,
script varchar(4000) COLLATE Latin1_General_CI_AS NULL,
PRIMARY KEY CLUSTERED (SERVERNAME,NAME,I)
)
exec sp_getthelogins @Radhe ='#Jagannatha'
select *
from #Jagannatha
that would show you something like this:
partial view with names changed to protect the innocent.
You could also script a single login, and avoid printing of the script - just look at the parameters.
have fun!
PS -> Sometimes you need to look at permissions on master and msdb when referring to server side permissions of an specific login.
