How can sysname = null in this SQL Server system stored procedure if sysname cannot be null?

Question

I read that sysname cannot be null, yet in the definition of the system procedure sp_grantdbaccess, I see the assignment of null to one of the procedure's arguments (if I read this right). How can this be?

ALTER PROCEDURE [sys].[sp_grantdbaccess]
    @loginame   sysname,
    @name_in_db sysname = NULL OUT

Answer 1

"SYSNAME cannot be NULL" is just not true. The linked question's answer is correct when it says that it's equivalent to NVARCHAR(128) NOT NULL as a default -- and then effectively only in column definitions. Compare:

-- When not specified, columns are NULL
SET ANSI_NULL_DFLT_ON ON  

-- Works
CREATE TABLE T(N NVARCHAR(128)); INSERT T DEFAULT VALUES; SELECT * FROM T
GO
DROP TABLE T
GO

-- When not specified, columns are NOT NULL
SET ANSI_NULL_DFLT_ON OFF

-- Error: can't insert NULL
CREATE TABLE T(N NVARCHAR(128)); INSERT T DEFAULT VALUES; SELECT * FROM T
GO
DROP TABLE T
GO

And now try the same with SYSNAME:

-- When not specified, columns are NULL
SET ANSI_NULL_DFLT_ON ON  

-- Error: SYSNAME is NOT NULL, regardless of defaults
CREATE TABLE T(N SYSNAME); INSERT T DEFAULT VALUES; SELECT * FROM T
GO
DROP TABLE T
GO

But this does not mean SYSNAME cannot be NULL, all we have to do is say it may be:

-- Works
CREATE TABLE T(N SYSNAME NULL); INSERT T DEFAULT VALUES; SELECT * FROM T
GO
DROP TABLE T
GO

In almost all other contexts where a type is used (variables, stored procedure parameters), we cannot specify NULL or NOT NULL and NULL values are always allowed, so this NOT NULL metadata is very rarely relevant. It's no accident that the above code is using regular tables: if you try the same thing with a table variable, you'll find that ANSI_NULL_DFLT_ON is ignored and NULL is always the default for columns, if not specified, so the only relevant cases are:

-- Can't insert NULL
DECLARE @T TABLE (N SYSNAME); INSERT @T DEFAULT VALUES; SELECT * FROM T@
GO

-- OK
DECLARE @T TABLE (N SYSNAME NULL); INSERT @T DEFAULT VALUES; SELECT * FROM @T
GO