x

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
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?
more ▼

asked Jun 08 '11 at 03:45 AM in Default

Bhuvans gravatar image

Bhuvans
220 17 19 21

can you post the exact sql you are using including the defintion and setting of the domainusername variable?
Jun 08 '11 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 '11 at 04:00 AM Kev Riley ♦♦
yes u r right
Jun 08 '11 at 04:25 AM Bhuvans
(comments are locked)
10|1200 characters needed characters left

1 answer: sort oldest

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][2].

[2]: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Little-Bobby-Tables-SQL-Injection-and-EXECUTE-AS.aspx
more ▼

answered Jun 08 '11 at 04:03 AM

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

thanks ... i think, i need to groom my basics more and more
Jun 08 '11 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x21

asked: Jun 08 '11 at 03:45 AM

Seen: 996 times

Last Updated: Jun 08 '11 at 10:08 AM