Create SQL users from within Navision

Pidi

With 4.x (currently migrating) I love to touch every user three times instead of one time before ... :-(

Because of that I tried the following:

1. Storing all passwords in readable form in a seperate column within Tab13
2. Calling a stored procedure on the SQL Server from within Navision
3. passing username and pw with that sp_
4. Writing a TSQL script to call

Here´s the Navision code:
MakeSqlUser(InCodUsername : Code[10];InTxtPassword : Text[30])
HKchar := 39;
HK := FORMAT(HKchar);

CREATE(ADOConn);
REPEAT UNTIL VARIABLEACTIVE(ADOConn);

Serveradresse := 'yourserver';
Benutzername := 'sa';
passwort := 'yourpw';

OpenCMD := 'Provider=SQLOLEDB.1;Data Source=' + Serveradresse + ';' +
'User ID=' + Benutzername + ';Password=' + passwort + ';Initial Catalog=master';
ADOConn.Open(OpenCMD);

CREATE(ADOCmd);
REPEAT UNTIL VARIABLEACTIVE(ADOCmd);

ADOCmd.ActiveConnection(ADOConn.ConnectionString);

cmdLine := '{call master.dbo.bp_NewNavUser(' +HK +InCodUsername +HK +',' +HK +InTxtPassword +HK+')}';

ADOCmd.CommandText(cmdLine);
ADOCmd.CommandTimeout(20);
ADOCmd.CommandType(1);
ADOCmd.Execute;

ADOConn.Close;

CLEAR(ADOCmd);
CLEAR(ADOConn);



And the TSQL script stored in MASTER DB of the server:
CREATE PROCEDURE dbo.bp_NewBosUser
@loginname sysname,
@password varchar(30)
AS

IF (SELECT name FROM master.dbo.sysxlogins WHERE Name = @loginname) = @Loginname
BEGIN
exec bosfood.dbo.sp_revokedbaccess @loginname;
exec sp_droplogin @loginname;
END

exec sp_addlogin @loginname, @password
exec sp_defaultlanguage @loginname, N'Deutsch'
exec bosfood.dbo.sp_grantdbaccess @loginname, @loginname
exec bosfood.dbo.sp_addrolemember N'db_owner', @loginname
GO


And it does, what it´s supposed to do ...

The Navision code came from trial and error, the TSQL code was recorded with the Profiler.

I don´t have much experience with SQL server by now, and that´s the reason I´m looking for some feedback on this.

If anyone´s interested in the code (Nav object regarding the AC vars) send me an email.

Pidi

ara3n

I've haven't tried it but, you should add it to tips and trick section.

geosava

Hi,
How did you define the ADOConn variable in your code?? ](*,)
Which dll??

Thanxs!!!