Adding New Logins to SQL using Delphi 2007

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Adding New Logins to SQL using Delphi 2007

Author

Topic

Jerbear
Starting Member

11 Posts

Posted - 2011-03-17 : 06:16:45

Hi Guys,
Have just joined this forum and this is my first request for help.
I want to add new logins to SQL Server using Delphi Code as shown below. How ever it falls over on the password line. If I strip the password line down to MyQuery.SQL.Add('WITH PASSWORD=');I then get an error on the Default_Database line.

Can anyone help here.? Using Delphi 2007 and CoreLab's SDAC components.

Begin
MyQuery.SQL.Clear;
MyQuery.SQL.Add('CREATE LOGIN [John Doe]');
MyQuery.SQL.Add('WITH PASSWORD=N'+'mypassword');
MyQuery.SQL.Add('DEFAULT_DATABASE=[MyDatabase]');
MyQuery.SQL.Add('DEFAULT_LANGUAGE=[British]');
MyQuery.SQL.Add('CHECK_EXPIRATION=OFF');
MyQuery.SQL.Add('CHECK_POLICY=OFF');
MyQuery.SQL.Add('ALTER LOGIN [John Doe] DISABLE');
MyQuery.Execute;
end;

Many thanks.

jerbear

If it's not Broke...Don't Fix it!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-17 : 07:02:35

Can you change the password line to this:

MyQuery.SQL.Add('WITH PASSWORD=N'+''mypassword'');

My guess is that the password is not surrounded by quotes, as it should be. Once you get those in there it should work.

Jerbear
Starting Member

11 Posts

Posted - 2011-03-17 : 07:23:40

Hi Rob.

Tried that...same error...

MyProgram.exe raised exception class EMSError with message "Incorrect syntax near 'N','

quote:

Originally posted by robvolk

Can you change the password line to this:

MyQuery.SQL.Add('WITH PASSWORD=N'+''mypassword'');

My guess is that the password is not surrounded by quotes, as it should be. Once you get those in there it should work.

If it's not Broke...Don't Fix it!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-17 : 07:27:56

Is there a way you can print the SQL it's trying to run, and paste it into an SSMS query window? You can then fiddle with the syntax until it's correct, then modify the code to produce the same statement.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-17 : 07:34:27

You need a few commas in the SQL string you are building: one after the password, one after the default language etc.

Jerbear
Starting Member

11 Posts

Posted - 2011-03-17 : 07:57:08

quote:
Originally posted by sunitabeck

You need a few commas in the SQL string you are building: one after the password, one after the default language etc.

Ok It now looks like this and I still get the same error.

MyQuery.SQL.Clear;
MyQuery.SQL.Add('CREATE LOGIN [John Doe]');
MyQuery.SQL.Add('WITH PASSWORD=N"letmein",');
MyQuery.SQL.Add('DEFAULT_DATABASE=[IPSDB],');
MyQuery.SQL.Add('DEFAULT_LANGUAGE=[British],');
MyQuery.SQL.Add('CHECK_EXPIRATION=OFF,');
MyQuery.SQL.Add('CHECK_POLICY=OFF,');
MyQuery.SQL.Add('ALTER LOGIN [John Doe] DISABLE');
MyQuery.Execute;

If it's not Broke...Don't Fix it!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-17 : 09:06:21

Shoulda listened to Rob in the first place - usually he knows what he is talking about, and usually I don't :--)

"Is there a way you can print the SQL it's trying to run, and paste it into an SSMS query window? You can then fiddle with the syntax until it's correct, then modify the code to produce the same statement."

One thing I do see is that you alter login statement is a separate statement, so you should separate that from the rest by a semi-colon rather than a comma as in

MyQuery.SQL.Add('CHECK_POLICY=OFF;');

Also, make sure that the quotes in this are all single quotes, and not any double quotes, as in

MyQuery.SQL.Add('WITH PASSWORD=N''letmein'',');

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-17 : 09:34:49

quote:
Shoulda listened to Rob in the first place - usually he knows what he is talking about

You must LOVE to gamble.

Jerbear
Starting Member

11 Posts

Posted - 2011-03-17 : 10:27:13

This entered as a query in SQL Server MS works a treat...

CREATE LOGIN [JOHN DOE] WITH PASSWORD='LETMEIN', DEFAULT_DATABASE=[NBCDB], DEFAULT_LANGUAGE=[BRITISH], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;

But trying to do it in Delphi like this returns the same error everytime...

MyQuery.SQL.Clear;
MyQuery.SQL.Add('CREATE LOGIN [Jane Doe] ');
MyQuery.SQL.Add('WITH PASSWORD="letmein", ');
MyQuery.SQL.Add('DEFAULT_DATABASE=[NBCDB], ');
MyQuery.SQL.Add('DEFAULT_LANGUAGE=[British], ');
MyQuery.SQL.Add('CHECK_EXPIRATION=OFF, ');
MyQuery.SQL.Add('CHECK_POLICY=OFF;');
MyQuery.Execute;

It falls over on the password line with or without the N parameter.

EUREKA! I got it.....

MyQuery.SQL.Clear;
MyQuery.SQL.Add('CREATE LOGIN [Jane Doe] ');
MyQuery.SQL.Add('WITH PASSWORD=''letmein'', ');
MyQuery.SQL.Add('DEFAULT_DATABASE=[NBCDB], ');
MyQuery.SQL.Add('DEFAULT_LANGUAGE=[British], ');
MyQuery.SQL.Add('CHECK_EXPIRATION=OFF, ');
MyQuery.SQL.Add('CHECK_POLICY=OFF;');
MyQuery.Execute;

This change to the password did the trick. One pair of single quotes either side of the password.

Thanks guys....

If it's not Broke...Don't Fix it!

Jerbear
Starting Member

11 Posts

Posted - 2011-03-18 : 17:12:24

Hi Guys,

Back again. Just to explain me reason for my earlier request.
My App creates a user login in SQL server for each staff member and places a shortcut on their desktop which is a link to my DB program.

The routine works fine now but one more thing I need to be able to program and can't figure out how to do it, or if it's even possible.

USER MAPPING.
If a staff member runs the routine I created he/she can create their own login name on Sql Server with a named DB, however, they can't login until I go to SQL SERVER M.S., open the properties of the created user and set the USER MAPPING.

Can this be done in code? Anybody...?

Thanks.

If it's not Broke...Don't Fix it!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-18 : 17:20:10

Add this:

USE myDatabase
CREATE USER myUser FROM LOGIN myLogin

Substitute the correct names where a "my-" reference appears.

Jerbear
Starting Member

11 Posts

Posted - 2011-03-18 : 18:05:05

Thanks for the response.

If I understand you correctly shouldn't this code work.....

CREATE LOGIN [JOHN DOE]
WITH PASSWORD='LETMEIN',
DEFAULT_DATABASE=[NBCDB],
DEFAULT_LANGUAGE=[BRITISH],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF;

The above code creates the user no problems......

But this code, If I've applied it correctly does not set the
USER MAPPING.

USE NBCDB
CREATE USER [John Doe] FROM LOGIN [John Doe];

If it's not Broke...Don't Fix it!

Jerbear
Starting Member

11 Posts

Posted - 2011-03-18 : 18:11:38

My Apologies.....it does work..

I,ve just tried it again. CASE seems to be important!!!

How about setting the ROLE as well. I meant to ask that in my previous post.

This tries to create a NEW role. I just want to use and existing role so I'm going to try USE instead of CREATE'

CREATE ROLE db_datareader AUTHORIZATION [JOHN DOE];

If it's not Broke...Don't Fix it!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-18 : 19:22:56

Role assignment is different. You can't create db_datareader as it already exists. You want to assign or add that user to a role, using the sp_addrolemember procedure:

EXEC sp_addrolemember 'db_datareader', 'myUser'

Jerbear
Starting Member

11 Posts

Posted - 2011-03-19 : 04:26:44

Many thanks guys,

I managed to work out the last one before you posted.
Currently attempting MS offical course 2780B, Maintaining a MS SQL Server 2005 Database.

There wasn't much in the MS Offical course book on this command, but just enough to point me in the right direction.

This forum is now No.1 on my favourites list.

So my completed Delphi Code now looks like this, and it works.
Just in case anyone else is interested....
NOTE: Using Delphi 2007 and SDAC SQL components.

MSQuery1.SQL.Clear;
MSQuery1.SQL.Add('CREATE LOGIN [JOHN DOE] ');
MSQuery1.SQL.Add('WITH PASSWORD=''letmein'', ');
MSQuery1.SQL.Add('DEFAULT_DATABASE=[MYDB], ');
MSQuery1.SQL.Add('DEFAULT_LANGUAGE=[British], ');
MSQuery1.SQL.Add('CHECK_EXPIRATION=OFF, ');
MSQuery1.SQL.Add('CHECK_POLICY=OFF;');
MSQuery1.SQL.Add('USE MYDB ');
MSQuery1.SQL.Add('CREATE USER [JOHN DOE] FROM LOGIN [JOHN DOE]');

// Uncomment one or more of the following lines if you need to add a Built In Role

// MSQuery1.SQL.Add('EXEC sp_addrolemember 'db_datareader',[JOHN DOE]);
// MSQuery1.SQL.Add('EXEC sp_addrolemember 'db_datawriter',[JOHN DOE]);'
// MSQuery1.SQL.Add('EXEC sp_addrolemember 'db_denydatareader',[JOHN DOE]);
// MSQuery1.SQL.Add('EXEC sp_addrolemember 'db_denydatawriter',[JOHN DOE]);'
// MSQuery1.SQL.Add('EXEC sp_addrolemember 'db_owner',[JOHN DOE]);
// MSQuery1.SQL.Add('EXEC sp_addrolemember 'db_securityadmin',[JOHN DOE]);
// MSQuery1.SQL.Add('EXEC sp_addrolemember 'db_accessadmin',[JOHN DOE]);
// MSQuery1.SQL.Add('EXEC sp_addrolemember 'db_backupoperator',[JOHN DOE]);
//MSQuery1.SQL.Add('EXEC sp_addrolemember 'db_ddladmin',[JOHN DOE]);

MSQuery1.Execute;

If it's not Broke...Don't Fix it!

Jerbear
Starting Member

11 Posts

Posted - 2011-03-19 : 15:05:31

And I'm back......

Another problem has arisen! Go figure!!!

What if the user already has a LOGIN in SQL?

I've modified the code as follows.........

MSQuery1.SQL.Clear;

MSQuery1.SQL.Add('If not Exists (select name from master.sys.server_principals where name = 'JOHN DOE')');
MSQuery1.SQL.Execute;

I guess I need to execute the line above first to establish if the user/login exists, and return either true or false.
IF TRUE THEN

MSQuery1.SQL.Clear; // clear before we compile new query

MSQuery1.SQL.Add('BEGIN ');

MSQuery1.SQL.Add('CREATE LOGIN [JOHN DOE] ');
MSQuery1.SQL.Add('WITH PASSWORD=''letmein'', ');
MSQuery1.SQL.Add('DEFAULT_DATABASE=[MYDB], ');
MSQuery1.SQL.Add('DEFAULT_LANGUAGE=[British], ');
MSQuery1.SQL.Add('CHECK_EXPIRATION=OFF, ');
MSQuery1.SQL.Add('CHECK_POLICY=OFF;');
MSQuery1.SQL.Add('USE MYDB ');
MSQuery1.SQL.Add('CREATE USER [JOHN DOE] FROM LOGIN [JOHN DOE]');

MSQuery1.SQL.Execute; //should this be here or after the END statement

MSQuery1.SQL.Add('END')

I have one button on my form that when clicked executes 3 procedures.
This is the first one which creates a login, The second, creates a shortcut in 3 locations to start the DB application and the 3rd creates an INI file from which the DB App gets it login info among other things. The last two depend on this first one for some of the info required. Because of this problem where a login already exists the program halts and the last two procedures do not get executed.

and now the SQL Code......

If not Exists (select name from master.sys.server_principals where name = 'John Doe')
BEGIN
CREATE LOGIN [John Doe]
WITH PASSWORD='letmein',
DEFAULT_DATABASE=[NBCDB],
DEFAULT_LANGUAGE=[British],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF;
USE [NBCDB]
CREATE USER[John Doe] FROM LOGIN [John Doe]
END

This works and displays this message.....
Msg 15023, Level 16, State 1, Line 10
User, group, or role 'John Doe' already exists in the current database.

Surely in SQL there must be some sort of IF THEN ELSE that could be used.

How can I capture this error in Delphi so that it doesn't halt the flow of my program.

If it's not Broke...Don't Fix it!

Jerbear
Starting Member

11 Posts

Posted - 2011-03-20 : 06:21:12

I've posted a screenshot here.....

If it's not Broke...Don't Fix it!

Jerbear
Starting Member

11 Posts

Posted - 2011-03-21 : 07:01:21

No delphi programmers out there with any ideas yet....????

If it's not Broke...Don't Fix it!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources