question

Wood Chuck avatar image
Wood Chuck asked

How do Create a Login name with non alpha-numeric characters programmatically?

HI, I'm creating a program to create logins and user permissions for SQL Server. I attempted to use parameterized queries but I keep getting syntax errors complaining about an error by/near @username and or @mypassword. I've tried the following:

"Create Login @username with password @mypassword"
and
"Create Login @username with password '@mypassword'" - which is believe is incorrect.

I know the rest of the logic (supplying parameters) is correct because I use a class for creating and using parameterized queries all over my code and this is exactly the same.

I read somewhere that parameterized queries are not supported for Create/alter/drop login commands. Is this true?

How can I somehow create logins using characters such as ' ' and ''' and '%' and '(' in the user name and password as I can do from SSMS?

Thank you!!

login
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

As far as I know, you cannot use parameters for CREATE LOGIN.

You could achieve this by using dynamic sql.

That aside - the syntax for CREATE LOGIN is slightly wrong, it should read CREATE LOGIN login_name WITH PASSWORD ='password' - you are missing the '='.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Kev: Great catch of the missing = in the syntax, much better than the one attempted by Robert Green :(
0 Likes 0 ·
Oleg avatar image
Oleg answered

Kev's answer is absolutely correct, dynamic sql is going to have to be your friend to accomplish the task. Here is one example, which creates logins by executing a stored procedure expecting a single parameter specifying all logins that you want to create at once in xml format. In reality, you can call the proc from your program, but I will provide a simple test on how to execute it from query window. Here is the proc definition:

create proc dbo.test_create_logins(@xml xml)
as

begin

    declare @sql varchar(max);
    set @sql = '';

    select 
        @sql = @sql + 'create login [' + item.value('name[1]', 'varchar(50)') +
        '] with password = ''' + 
               replace(item.value('pwd[1]', 'varchar(50)'), '''', '''''') + 
        ''';' + char(13) + char(10)
        from @xml.nodes('//*/item') r(item);

    exec (@sql);

end;
go

Now we can test it by passing xml containg data for 4 logins:

declare @xml xml;

set @xml = '
<items>
    <item>
        <name>bozo_the_clown1</name>
        <pwd>wringleybros1</pwd>
    </item>
    <item>
        <name>bozo_the_clown2</name>
        <pwd>wringleybros2</pwd>
    </item>
    <item>
        <name>bozo_the_clown3</name>
        <pwd>wringleybros3</pwd>
    </item>
    <item>
        <name>bozo_the_clown4</name>
        <pwd>wringleybros4</pwd>
    </item>
</items>
';

exec dbo.test_create_logins @xml;

The procedure will handle logins and passwords with non-alphanumeric characters. Hope this example will help you.

Oleg

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.