I got Message, that I has incorrect Syntax in my code
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near “SID”.
I do know what is wrong in my code:
DECLARE @principal_id INT;
DECLARE @name NVARCHAR(128);
DECLARE @type_desc NVARCHAR(60);
DECLARE @create_date DATETIME;
DECLARE @modify_date DATETIME;
DECLARE @default_database_name NVARCHAR(128);
DECLARE @default_language_name NVARCHAR(128);
DECLARE @sid VARBINARY(85);
DECLARE @is_disabled BIT;
DECLARE @sql NVARCHAR(MAX);
-- declare Cursor
DECLARE login_cursor CURSOR FOR
SELECT principal_id, name, type_desc, create_date, modify_date,
default_database_name, default_language_name, sid, is_disabled
FROM [AdventureWorks2014].dbo.SQLLogins;
-- open Cursor
OPEN login_cursor;
-- Initial retrieval of data from the cursor
FETCH NEXT FROM login_cursor INTO @principal_id, @name, @type_desc, @create_date,
@modify_date, @default_database_name, @default_language_name, @sid, @is_disabled;
-- loop Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
-- Format SID as hexadezimal
DECLARE @sid_hex NVARCHAR(200);
SET @sid_hex = '0x' + CONVERT(NVARCHAR, CONVERT(VARBINARY, @sid), 2);
-- Generate the SQL command
SET @sql = 'CREATE LOGIN [' + @name + '] WITH SID = ' + @sid_hex
+ ', DEFAULT_DATABASE = [' + @default_database_name + ']'
+ ', DEFAULT_LANGUAGE = [' + @default_language_name + ']'
+ ', CHECK_POLICY = OFF';
-- Output of SQL command for monitoring
PRINT @sql;
-- Execute the SQL command
EXEC sp_executesql @sql;
-- Get next row of data from cursor
FETCH NEXT FROM login_cursor INTO @principal_id, @name, @type_desc, @create_date,
@modify_date, @default_database_name, @default_language_name, @sid, @is_disabled;
END;
-- Close and release cursor
CLOSE login_cursor;
DEALLOCATE login_cursor;
I am currently learning T-SQL and I have created a table of logins for backup. And I wanted to get data back from this table. With print I get data
CREATE LOGIN [sa] WITH SID = 0x01, DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english], CHECK_POLICY = OFF,
Unfortunately I also get a message that I have the wrong syntax. And I don’t know if my cursor is working properly
I tried to execute the SQL
EXEC sp_executesql N'CREATE LOGIN [sa] WITH SID = 0x01, DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english], CHECK_POLICY = OFF';
and always shows incorrect syntax
New contributor
randia is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1