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 'proddbusr'@'%' IDENTIFIED BY 'thepassword'; CREATE USER 'proddbusr'@'localhost' IDENTIFIED BY 'thepassword'; USE proddb; GRANT ALL PRIVILEGES ON *.* TO 'proddbusr'@'localhost' IDENTIFIED BY 'thepassword'; GRANT ALL PRIVILEGES ON *.* TO 'proddbusr'@'%' 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
SQL Server also chokes on
*.* in the GRANT statement.
Can someone who is familiar with SQL Server, help me convert all these statements to the equivalent statements for SQL Server?
Gone through the links provided by bartlaarhoven.
CREATE DATABASE proddb; CREATE LOGIN proddbusr with PASSWORD = 'thepassword'; CREATE USER proddbusr for LOGIN proddb;
Upto this works.
However, it's not clear how username@'%' and username@'localhost translates in SQL Server.
Also after that.
The next statement has problems.
GRANT ALL PRIVILEGES ON *.* to proddb;
SQL Server doesn't recognize ..
I changed this to
GRANT ALL PRIVILEGES to proddb;
This gives me the error
The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity..
Anyway, in SQL Server Management Express, I did the following. Right Click on user proddb inside database proddb -> Properties. Give all possible permissions in both the 'Owned Schemas' and 'Database Role Memberships' windows except for the 2 deny permissions in each. Is this something similar to `Grant all privileges'?