From the Security tab, right-click on Logins and click on new login.
In the window that opens, we give a name to Login from Login Name.
I recommend that you create your login name by putting a User statement at the end of the database name, starting from the principle that everything is a standard in database administration.
For example, the user who will be connected to the “deneme” database can be a denemeUser.
You can define 3 types of login.
- Login With SQL Authentication
- Login With Windows Authentication
- Server Login
We can say SQL Login to login which is using SQL Authentication.
The type of login you can use without any relevant with Active Directory.
The type of login that uses Windows Authentication is the login type that uses Active Directory to check passwords.
There are 3 options that can be selected when you select SQL Server Authentication.
Enforce Password Policy: Determines whether to use the account policy on the server where the Intance is located
Enforce Password Expiration: Determines whether the expiration period defined in the account policy on the server where the Intance is located will be forced or not forced.
User must change password at next login: Determines whether you will force login to change the password the first time it is connected.
In Windows Authentication these options will be passive. Because password checking is done in the active directory.
Information about Server Login can be found in the article “Create Server Login(Do not type a password in web config)“.
After making the necessary selections as SQL Login or Windows Login, you can go to the Server Roles tab on the left side.
If you want to authorize Login at the server level, you can authorize one of these server level roles.
The most important of these roles is sysadmin.
Sysadmin is the most authoritative user in SQL Server.
It can do every job. It should not be given to anyone other than DBAs.
Even DBAs should even gain this authority after attaining a level of knowledge and experience.
Details of other roles can be found on the link below.
https://msdn.microsoft.com/en-us/library/ms188659.aspx
As a personal opinion, any login other than DBAs should not be authorized as a role at the server level.
After Server Level Roles, you will see the User Mapping tab in the tab immediately below.
Here you can select the database you want to authorize and give it what you want from the db roles below.
You can find the details of the roles in the database level from the link below.
https://msdn.microsoft.com/en-us/library/ms189121.aspx
At the database level, we give public permission and then right-click on the database and call properties-> permission.
From here we can give more detailed and specific authorization by selecting the relevant login.
Also, by selecting the with grant option with an authorization to grant here, the login we authorize can also authorize another login with this authorization
We can also do schema-based authorization. SQL Server tables are created by default in the dbo schema.
Sometimes we need to define different schemas under the same database.
SQL Server provides us to this flexibility.
For example, outside of the dbo schema we have defined a schema named x and a login named denemeUser, and we want to give the select permission to denemeUser on the tables created under the schema x.
We do this in the following way.
Under the database we want to authorize, we find the relevant schema in the Security-> Scheme tab and right-click and properties.
We click on Search-> Browse and select the user we have given public authority via database level role.
And we can give the specific authority we want at the schema level.
In our example, we gave the select privilege in the schema x to “denemeUser” as below.
We follow the steps below so that login can create a table or function on this schema.
Right click on the database and click properties.
And we find the user by clicking search in the permission tab as below.
Then we grant create table and create function permissions as follows.
Since we do not authorize in the Dbo schema, we will only have these authorities in their own schema.
