Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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 'proddb'@'localhost'.

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?

UPDATE:

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.

USE proddb;

This works.

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'?

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

SQL Server works differently with this. You create a LOGIN with a password for a user, which is independent of its hostname or something like that.

Then you grant access to the database by creating a USER on the database for a certain LOGIN.

Then you GRANT rights to the USER.

Read these links and you'll get what you need.

share|improve this answer
    
Thank you. I'll try this out. What about the @'%' and @'localhost'? Is there any equivalent for that in SQL Server? Also for the *.*? –  user93353 Jan 21 '13 at 11:45
    
Its also not clear what's the difference between 'Create user abcd from login abcd` and 'Create user abcd for login abcd`? –  user93353 Jan 21 '13 at 11:51
    
The "for" and "from" are the same, just different syntax to comply to different (legacy) SQL languages. As I said, the @'%' or @'localhost' don't exist in SQL server; if you have a valid login and you can make a network connection to the server, you can login. –  bartlaarhoven Jan 21 '13 at 12:16
    
For the GRANT ALL PRIVILEGES ON *.*, you can better add this user (so the user on database-level, not the login) to the role sysadmin. This is a predefined role where you can do everything you'll need, so just adding this user to this role will give you the necessary permissions. See msdn.microsoft.com/en-us/library/ms189775.aspx (for SQL 2012; older versions use msdn.microsoft.com/en-us/library/ms187750(v=sql.105).aspx. Or just the right-click menu on the database user in SQL Server Management Studio. –  bartlaarhoven Jan 21 '13 at 12:17
    
There doesn't seem to be a 'sysadmin' role at the database level. I right clicked on the user & gave it all other roles in both the 'Owned Schemas' and 'Database Role Memberships' windows except for the 2 deny roles in each. –  user93353 Jan 21 '13 at 12:46
show 1 more comment

On SQL Server you create an Login/User look this:

USE master
GO
CREATE LOGIN USER01
WITH PASSWORD =  'PASSword'

Then you use GRANT like this to grant alter to any database:

USE master
GO 
GRANT ALTER to any DATABASE TO USER01
GO
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.