I want to create two users
- App_dbo which is used to deploy scripts
- App_batch which is used to run jobs
I googled it and found SQL Script
I did not understand why I need to create a LOGIN.
What is the process to create a user ?
-
Isn't the answer on the question you found, clear enough? Based on your question, I think that you may be a bit confused about the difference between a User and a Login. A Login is an account on the SQL Server as a whole - someone who is able to log in to the server and who has a password. A User is a Login with access to a specific database. --> Everything is there!Thomas G– Thomas G2020-07-23 19:43:48 +00:00Commented Jul 23, 2020 at 19:43
-
Are you asking to create a user without a login? See: learn.microsoft.com/en-us/sql/t-sql/statements/…user13942986– user139429862020-07-23 19:44:49 +00:00Commented Jul 23, 2020 at 19:44
-
@ThomasG So, first I need to create login and then create user using those credentials?Chatra– Chatra2020-07-23 19:45:54 +00:00Commented Jul 23, 2020 at 19:45
-
@bbaird I want to know the best practiceChatra– Chatra2020-07-23 19:46:14 +00:00Commented Jul 23, 2020 at 19:46
-
@Chatra you'll need to update your question to reflect that.user13942986– user139429862020-07-23 19:49:03 +00:00Commented Jul 23, 2020 at 19:49
|
Show 4 more comments
1 Answer
Let me try to explain how the login and users work on a little example.
Imagine you are going to stay in a hotel, you've booked a room. At the reception they check your id and give you the key for your room. The id is your login, it is only checked once at the reception and you get access to whatever you are eligible for. If you paid for more rooms, you will get multiple keys.
In the SQL server, the rooms are the databases and the keys are your users. You can map multiple users to the login as long as the users are in a different databases - you get keys for all rooms you've paid for using just one id.
There is also a case where login doesn't have any users mapped. It can have permissions to create new databases or update infrastructure, but will not have access to the existing databases. This can be a case of a server admin. In the hotel example it is the technical maintenance employee who fixes pipes or electric wires. He has access to the hotel, but doesn't have keys for any rooms.
Therefore, login gives access to the sql server (hotel common areas), however, it doesn't give you access to any database. You need user(s) mapped to your login to get a database(s) access(es). Afaik, you can't map two users from the same db to one login (you only get one key for each room you've access too).
There are also other cases, like the AD one mentioned in the discussion below the question, where you can map multiple (external) identities to a single login etc, you can have users without login etc. I recommend to study documentation or get some good book explaining all possibilities. Good start might be here.
Hope this helps to understand how it works to a human being, there are plenty of answers how to set it up technically, for example the one you've referenced.
