When I right click on 'schemas' and then on 'create new schema' my SSMS only opens the query editor but on every tutorial I saw a pop up wizard opens instead. How can I access this popup wizard? I can't find the right setting. I'm currently following the course Developing SQL Databases on edx but I've also seen the popup wizard on youtube tutorials. I'm using the version 17.9.1 of SSMS
Many thanks in advance!
How can I access this popup wizard?
This popup is disabled for Azure SQL Database and Azure SQL Data Warehouse. It works for on-prem SQL Server.
Why? Probably because it didn't work as-is against Azure SQL DB/DW, and it wasn't deemed worth fixing. This is a really low-value feature, as Schemas don't really belong to Security and creating them in TSQL is so simple.
If you run Profiler you can see that SSMS runs the following batch:
declare @MasterPath nvarchar(512)
declare @LogPath nvarchar(512)
declare @ErrorLog nvarchar(512)
declare @ErrorLogPath nvarchar(512)
declare @Slash varchar = convert(varchar, serverproperty('PathSeparator'))
select @MasterPath=substring(physical_name, 1, len(physical_name) - charindex(@Slash, reverse(physical_name))) from master.sys.database_files where name=N'master'
select @LogPath=substring(physical_name, 1, len(physical_name) - charindex(@Slash, reverse(physical_name))) from master.sys.database_files where name=N'mastlog'
select @ErrorLog=cast(SERVERPROPERTY(N'errorlogfilename') as nvarchar(512))
select @ErrorLogPath=IIF(@ErrorLog IS NULL, N'', substring(@ErrorLog, 1, len(@ErrorLog) - charindex(@Slash, reverse(@ErrorLog))))
declare @SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT
Which wouldn't work on SQL Database or SQL Data Warehouse.
Rather than depend upon the GUI, use T-SQL. For a basic setup without additional security & configuration, (which is likely the case if you're following a tutorial), it's a simple query.
USE YourDatabaseName;
GO
CREATE SCHEMA YourSchemaName;
