I am trying to create a row security level over my table for the user department

Create Table Student_Table 
(
Title varchar(10) null,
DateofBirth DATE null,
BF1 varchar(10) null,
Language varchar(20) null,
Qualification varchar(20) null,
Programme varchar(20) null,
Curriculum varchar(20) null,
Level int null,
Department varchar(max) null
)

Department field contains
Level 1: General Staff
Level 2: IT Management
Level 3: Senior Managers

When I try create users 
CREATE USER Manager WITHOUT LOGIN;
CREATE USER Level 1: General staff WITHOUT LOGIN;
CREATE USER Level 2:IT Management WITHOUT LOGIN;
CREATE USER Level 3: Senior Managers WITHOUT LOGIN;
GO

I Get the following Error

Msg 102, Level 15, State 1, Line 35 Incorrect syntax near '1'. Msg 102, Level 15, State 1, Line 36 Incorrect syntax near '2'. Msg 102, Level 15, State 1, Line 37 Incorrect syntax near '3'.

Can someone please assist me.

CodePudding user response：

The error is simple. When name of an object should have space then whole name must be within [].

CREATE USER Manager WITHOUT LOGIN;
CREATE USER [Level 1: General staff] WITHOUT LOGIN;
CREATE USER [Level 2:IT Management] WITHOUT LOGIN;
CREATE USER [Level 3: Senior Managers] WITHOUT LOGIN;

CodePudding user response：

The first part of troubleshooting is break your code into pieces and work out which line failed first..

It was

CREATE USER Level 1: General staff WITHOUT LOGIN; right?

All the prior stuff is irrelevant.

This is the correct syntax:

CREATE USER [Level 1: General staff] WITHOUT LOGIN;

This needs to be repeated for your other user creation.

But you should really avoid identifiers with spaces or special characters altogether