String Conversion Problem in SQL

I want to pass my [Domain\username] as string .

 CREATE LOGIN [Domain\username] // Its raising an error here ,Expected ID or Quoted ID

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?

more ▼

asked Jun 08, 2011 at 03:45 AM in Default

avatar image

220 19 19 24

can you post the exact sql you are using including the defintion and setting of the domainusername variable?

Jun 08, 2011 at 03:57 AM Kev Riley ♦♦

As for the USE statement - what else are you trying to achieve here - you could use exec or sp_executesql but the context would only last until the statement completed - it wouldn't change your working database in the calling context.

Jun 08, 2011 at 04:00 AM Kev Riley ♦♦

yes u r right

Jun 08, 2011 at 04:25 AM Bhuvans
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Jun 08, 2011 at 04:03 AM

avatar image

Kevin Feasel
6.2k 4 8 15

thanks ... i think, i need to groom my basics more and more

Jun 08, 2011 at 04:28 AM Bhuvans
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 08, 2011 at 03:45 AM

Seen: 1726 times

Last Updated: Jun 08, 2011 at 10:08 AM

Copyright 2018 Redgate Software. Privacy Policy