String Conversion Problem in SQL

Question

I want to pass my [Domainusername] as string .

CREATE LOGIN [Domain\username] // Its raising an error here ,Expected ID or Quoted ID
FROM WINDOWS WITH DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english] 
END

And if i pass my database name as string , how to execute the below line. because it doesnt accepts string?

USE [MapOutTMEdgeReplicate]

How to overcome the above problem?

Answer 1

You could use dynamic SQL.

declare @sql nvarchar(max);
declare @username sysname;
declare @defaultdb sysname;
declare @defaultlanguage sysname;

set @username = 'TESTUSER';
set @defaultdb = 'master';
set @defaultlanguage = 'us_english';

set @sql = N'create login ' + quotename(@username) + ' from windows with default_database='
         + quotename(@defaultdb) + ', default_language='
         + quotename(@defaultlanguage);
exec sp_executesql @sql;

This isn't as good as the best way to use sp_executesql (that would involve passing in the parameters, but you would be right back where you started), but the quotename will help protect you from potential SQL injection attacks.

If you do end up using dynamic SQL to do this, be sure you have read Erland Sommerskog's article on the subject, as well as Kim Tripp's article on the same subject.

String Conversion Problem in SQL

Follow this question

Related Questions