Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Converting MySQL Steps to SQL Server Expand / Collapse
Author
Message
Posted Monday, January 21, 2013 4:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 21, 2013 7:15 PM
Points: 2, Visits: 12
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?
Post #1409483
Posted Monday, January 21, 2013 1:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 7,126, Visits: 12,727
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
Post #1409689
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse