Converting MySQL Steps to SQL Server

• vuser

  Grasshopper

  Points: 14

  More actions

  January 21, 2013 at 4:54 am

  #267135

  I am installing a product which needs a database - it can use any database as long as there is a jdbc connector available for it. However, the installation guide has instructions only on how to make it work with MySQL.

  I am running it with SQL Server Express.

  These are the instructions for MySQL

  CREATE DATABASE proddb;

  CREATE USER 'proddb'@'%' IDENTIFIED BY 'thepassword';

  CREATE USER 'proddb'@'localhost' IDENTIFIED BY 'thepassword';

  USE proddb;

  GRANT ALL PRIVILEGES ON *.* TO 'proddb'@'localhost' IDENTIFIED BY 'thepassword';

  GRANT ALL PRIVILEGES ON *.* TO 'proddb'@'%' IDENTIFIED BY 'thepassword';

  FLUSH PRIVILEGES;

  The CREATE DATABASE statement works fine on SQL Server.

  However, the next statement chokes. It seems like the db name shouldn't be in quotes in SQL Server. However, I am not sure what is the equivalent for '%' in SQL Server. Likewise for 'proddb'@'localhost'.

  SQL Server also chokes on *.* in the GRANT statement.

  Can someone help me convert all these statements to the equivalent statements for SQL Server?

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  More actions

  January 21, 2013 at 1:16 pm

  #1579365

  vuser (1/21/2013)

  ---

  I am installing a product which needs a database - it can use any database as long as there is a jdbc connector available for it. However, the installation guide has instructions only on how to make it work with MySQL.

  I am running it with SQL Server Express.

  These are the instructions for MySQL

  CREATE DATABASE proddb;

  CREATE USER 'proddb'@'%' IDENTIFIED BY 'thepassword';

  CREATE USER 'proddb'@'localhost' IDENTIFIED BY 'thepassword';

  USE proddb;

  GRANT ALL PRIVILEGES ON *.* TO 'proddb'@'localhost' IDENTIFIED BY 'thepassword';

  GRANT ALL PRIVILEGES ON *.* TO 'proddb'@'%' IDENTIFIED BY 'thepassword';

  FLUSH PRIVILEGES;

  The CREATE DATABASE statement works fine on SQL Server.

  However, the next statement chokes. It seems like the db name shouldn't be in quotes in SQL Server. However, I am not sure what is the equivalent for '%' in SQL Server. Likewise for 'proddb'@'localhost'.

  SQL Server also chokes on *.* in the GRANT statement.

  Can someone help me convert all these statements to the equivalent statements for SQL Server?

  MySQL requires you provide not only a username (e.g. proddb) but also the source IP address (or mask) it can connect from. SQL Server security does not care about source IP. If you need IP restrictions for logins you must implement your own mechanism within a LOGIN TRIGGER. Another difference is that SQL Server has Server Logins and Database Users where MySQL only has Users.

  This in MySQL:

  CREATE USER 'proddb'@'%' IDENTIFIED BY 'thepassword';

  Would be this in SQL Server:

  USE master;

  CREATE LOGIN proddb WITH PASSWORD = 'thepassword', DEFAULT_DATABASE = [thedatabasename];

  USE thedatabasename;

  CREATE USER [proddb] FROM LOGIN [proddb]; -- here the username can be different from the login name, your choice

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato