SQL Server – How to Fix SSMS Not Opening Popup Wizard to Create a New Schema

configurationschemasql serverssms

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!

Best Answer

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.

Related Solutions

Sql-server – Users cannot view tables in non-default schema in SSMS

Short answer: Don't use db_datareader or db_datawriter or their deny equivalents. They are for backwards compatibility only. Using them will cause issues like the one you are facing.

If you want to give principal Alice the SELECT, INSERT, UPDATE and DELETE permissions to all table-valued objects in schema Sales then use the following.

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Sales TO Alice ;

If you want to give principal Alice the SELECT, INSERT, UPDATE and DELETE permissions to all table-valued objects in all schemas then use the following.

GRANT SELECT, INSERT, UPDATE, DELETE TO Alice ;

Metadata visibility will then work correctly.

Sql-server – Restore Database wizard won’t open in SSMS 2014

I have seen that behavior myself in both SSMS 2012 and SSMS 2014. What it typically means is that the backup / restore history for the database is very long and it is taking a long time for the wizard to come up, because it needs to read the entire history.

Make sure you have a maintenance plan that truncates the history. To find out, run this query:

SELECT TOP 1 backup_start_date
FROM msdb.dbo.backupset WITH (NOLOCK)
ORDER BY backup_set_id ASC

That query will be quick, since it's sorted by the primary clustered key of the table. If you've got more than a month or two of history in there, it's probably time to clear it out. (Keep in mind that clearing the history will involve locks, so it can be a slow process if you're doing backups at the same time, or if your MSDB is on slow storage.)

Best Answer

Related Solutions

Sql-server – Users cannot view tables in non-default schema in SSMS

Sql-server – Restore Database wizard won’t open in SSMS 2014

Related Question