Issues running schema creation script

Question

In SSMS I have used Tasks/Generate Scripts on one server to create a script to recreate the schema only for a given database.
I added to this script some inserts for data in just a few of the tables.

I am now running this script on localhost SQLEXPRESS and I am getting syntax errors for the commented out parts of the script segment shown at the bottom of this post.
I am logged into localhost SQLEXPRESS as sa.

Is there a way to generate the script for a given SQLServer version / type? Or a way for it to check and support either?

Additionally I get these errors:

Msg 15007, Level 16, State 1, Line 144
'XXXYYYProdAdmin' is not a valid login or you do not have permission.
Msg 15007, Level 16, State 1, Line 147
'XXXZZZAdmin' is not a valid login or you do not have permission.
Msg 15151, Level 16, State 1, Line 151
Cannot add the principal 'XXXYYYProdAdmin', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 153
Cannot add the principal 'XXXZZZAdmin', because it does not exist or you do not have permission.

for these commands:

CREATE USER [XXXYYYProdAdmin] WITH DEFAULT_SCHEMA=[db_owner]
GO
/****** Object: User [XXXZZZAdmin] Script Date: 23/02/2022 10:45:32 ******/
CREATE USER [XXXZZZAdmin] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_owner] ADD MEMBER [XXXService]
GO
ALTER ROLE [db_owner] ADD MEMBER [XXXYYYProdAdmin]
GO
ALTER ROLE [db_owner] ADD MEMBER [XXXZZZAdmin]
GO

Given that I am logged in as sa I would have thought I should be able to create these
Is there something in the script generation settings that I should have set to make this part succeed?

Many thanks,

Andrew

Other errors mentioned above:

--ALTER DATABASE SCOPED CONFIGURATION SET ACCELERATED_PLAN_FORCING = ON;
--GO
--ALTER DATABASE SCOPED CONFIGURATION SET ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = OFF;
--GO
--ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ADAPTIVE_JOINS = ON;
--GO
--ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;
--GO
--ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = ON;
--GO
--ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = ON;
--GO
--ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = ON;
--GO
--ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 0;
--GO
--ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_ONLINE = OFF;
--GO
--ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_RESUMABLE = OFF;
--GO
--ALTER DATABASE SCOPED CONFIGURATION SET EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS = ON;
--GO
--ALTER DATABASE SCOPED CONFIGURATION SET GLOBAL_TEMPORARY_TABLE_AUTO_DROP = ON;
--GO
--ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = ON;
--GO
--ALTER DATABASE SCOPED CONFIGURATION SET INTERLEAVED_EXECUTION_TVF = ON;
--GO
--ALTER DATABASE SCOPED CONFIGURATION SET ISOLATE_SECURITY_POLICY_CARDINALITY = OFF;
--GO
--ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = OFF;
--GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
GO
--ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = ON;
--GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
--ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = OFF;
--GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
GO
--ALTER DATABASE SCOPED CONFIGURATION SET PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES = 1440;
--GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
GO
--ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;
--GO
--ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;
--GO
--ALTER DATABASE SCOPED CONFIGURATION SET VERBOSE_TRUNCATION_WARNINGS = ON;
--GO
--ALTER DATABASE SCOPED CONFIGURATION SET XTP_PROCEDURE_EXECUTION_STATISTICS = OFF;
--GO
--ALTER DATABASE SCOPED CONFIGURATION SET XTP_QUERY_EXECUTION_STATISTICS = OFF;
--GO

Answer 1

Regarding erro on user: The server logins don't exists and so the database user can't be created for the.

Regarding the others, I think they are more related to the use SQL Server version; which one do the server and your local instance have?

SELECT @@VERSION;  

In the script generator you can select if database setting should be scripted or not and you can select the target SQL Server version as well.

Answer 2

Those "logins" do not exist. Do not confuse "login" with "user". A "user" is a "login" linked to a database.

Answer 3

Thank you both so much for your advice - really appreciate it.

Is it possible to generate the script to craete the logins as well?
If not I guess I just add them to the script if I want all to be done through the script. I'll just search "create login" in MS SQL / T-SQL Docs, right?

I'll take another look at setting the SQL version for the commands (Database Settings) that are failing.
Alternatively by "...you can select if database setting should be scripted or not..." do you mean the commands that are failing are db settings and I can set the script not to generate these?
What are the consequences of this? Gets default settings for the instance created on? I guess I can just make this clear in the script comment and offline discussion for who runs it.

Thanks again for you help.

Andrew

Answer 4

Hi AndrewHardy-5421,
Welcome to Microsoft Q&A.

Is it possible to generate the script to create the logins as well?

You can generate the script including the logins by setting the “Script Logins” to True in the Generate Scripts-> Set Scripting Options. Please check the screenshot as Tom posted.
You can also use the following query from this article to script login and user permissions:

SET NOCOUNT ON  
-- Scripting Out the Logins To Be Created  
SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+  
			   CASE   
					WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = '   
						+ CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END  
					ELSE ' FROM WINDOWS WITH'  
				END   
	   +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]  
FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL  
		ON SP.principal_id = SL.principal_id  
WHERE SP.type IN ('S','G','U')  
		AND SP.name NOT LIKE '##%##'  
		AND SP.name NOT LIKE 'NT AUTHORITY%'  
		AND SP.name NOT LIKE 'NT SERVICE%'  
		AND SP.name <> ('sa');  
  
-- Scripting Out the Role Membership to Be Added  
SELECT   
'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');  
  
  
-- Scripting out the Permissions to Be Granted  
SELECT   
	CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION'   
		THEN SrvPerm.state_desc   
		ELSE 'GRANT'   
	END  
    + ' ' + SrvPerm.permission_name + ' 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   
	JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_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.name <> ('sa');  
  
SET NOCOUNT OFF  

Best Regards,
Amelia

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.