Styjun

I am creating a GUI through Eclipse and am using Java; and the application connects to an SQL server database. I managed to add new users directly using Microsoft SQL server with their respective securables or restrictions.

However, I would like to add new users through the GUI itself and be able to determine which table they can access. Or at least just add new users and then ill figure out how to determine which table they can access. Im running into a problem converting the SQL code into Java, ( I am new to all this after all).

Thank you in advance,

Following is the SQL Code I found used in SQL Server to add new login and then a new User, the idea is to replace "NewAdminName" and "ABCD" with the actual user input directly from the GUI:

CREATE LOGIN NewAdminName WITH PASSWORD = 'ABCD'
GO

Use BEPAWI;
GO

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'NewAdminName')
BEGIN
 CREATE USER [NewAdminName] FOR LOGIN [NewAdminName]
 EXEC sp_addrolemember N'db_owner', N'NewAdminName'
END;

Following is the JAVA code Im am currently working on with Eclipse:

JButton btnNewButton = new JButton("Enter");
 btnNewButton.addActionListener(new ActionListener() 
 public void actionPerformed(ActionEvent arg0) 

 try 

 String username = "";
 String password = "";
 username = UsernametextField.getText().trim();
 password = PasswordtextField.getText().trim();

 if (username.equals("")

 catch (SQLException se) 
 
 //handle errors for JDBC
 se.printStackTrace();
 
 catch (Exception a) //catch block
 
 a.printStackTrace();
 

 
 );

I believe my problem is this line of code:

resultSetInt = statement.executeUpdate("CREATE LOGIN '"+username+"' WITH PASSWORD = ''"+password+"'' GO USE BEPAWI GO IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''"+username+"'') BEGIN CREATE USER ['"+username+"'] FOR LOGIN ['"+username+"'] EXEC sp_addrolemember N'db_owner', N''"+username+"'' END;) VALUES('"+username+"', '"+password+"');");

Maybe I have to add all the columns from the SQL table? such as the
sys.database_principals and FROM sys.server_principals

Thank you again,

Ok, so I figured it out. WOHOOO!!!

Im adding the code in case someone else needs it.

Now to figure out how to restrict certain tables to each new user....

Thank you,

try password.equals(""))
 
 JOptionPane.showMessageDialog(null," name or password is wrong","Error",JOptionPane.ERROR_MESSAGE);
 
 else
 
 connection = DriverManager.getConnection(AdminMenu.DATABASE_URL, AdminMenu.UserName, AdminMenu.Password);
 statement = connection.createStatement();


 resultSetInt = statement.executeUpdate("CREATE LOGIN "+username+" WITH PASSWORD = '"+password+"'");
 resultSetInt = statement.executeUpdate("IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'"+username+"') CREATE USER ["+username+"] FOR LOGIN ["+username+"] EXEC sp_addrolemember N'db_owner', N'"+username+"'");

 //("CREATE LOGIN NewAdminName WITH PASSWORD = 'ABCD' GO USE BEPAWI GO IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'NewAdminName') BEGIN CREATE USER [NewAdminName] FOR LOGIN [NewAdminName] EXEC sp_addrolemember N'db_owner', N'NewAdminName' END;) VALUES('"+username+"', '"+password+"');");

 String SMessage = "Record added for "+username;

 // create dialog ox which is print message
 JOptionPane.showMessageDialog(null,SMessage,"Message",JOptionPane.PLAIN_MESSAGE);
 //close connection
 ((java.sql.Connection)connection).close();
 

 

 catch (SQLException se) 
 
 //handle errors for JDBC
 se.printStackTrace();
 
 catch (Exception a) //catch block
 
 a.printStackTrace();
 

 }
 });