SQL Login

Database servers are the most important server in the company as they contains the critical and important information of company that’s why database security plays an important role in DBA’s career

Database servers are very crucial that’s why we have to give proper permissions and access to the user. To enter into the SQL Server you must have a login if SQL Server login has not been set up, authentication fails and the user receives an error. The user is granted access to the server after his login has been authenticated. Database security then validates the user’s permissions by checking the database user accounts on the server.

Here we are going to learn the types of access and permission that we have to give to the user as per the database security best practices.

There two types of login from which you can access the database

Windows Login

            Permissions are inherited from windows 

SQL Login
              You have to create a native SQL Login and have to give a proper permission to that user on  database.

Login Creation for SQL Server 2005

Click Start > Programs > Microsoft SQL Server 2005 > SQL Server Management Studio.
In the Registered Servers panel, expand the Database Engine tree. 

Expand the Security folder in the Object Explorer pane. Right-click the Logins folder and click New Login.

Provide a new login name in the Login Name field.

Select SQL Server Authentication and provide the Password.

Change the Default Database to the one you want the login name assigned to.

Leave the Default Language set to default.

Select Server Roles in the Select a page pane and verify that all Server roles are not selected.

Select User Mapping in the Select a page pane. In the Users mapped to this login field, select the databases you want the user assigned to.

In the Default schema column, specify the user name for the default schema. The user name for the schema must be the same as the user login name.

In the database role membership pane, verify that only public and db-owner are selected.

Click OK.

Syntax for Windows Login

CREATE LOGIN [SQL\Sqlindex] FROM WINDOWS

WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

Syntax for SQL Login

create login Sqlindex with password ='*********',

DEFAULT_DATABASE=[master],

DEFAULT_LANGUAGE=[us_english],

CHECK_EXPIRATION=ON, CHECK_POLICY=ON

EXEC sys.sp_addsrvrolemember 'Sqlindex','dbcreator'

Alter Login Syntax

      ALTER LOGIN login ENABLE

      ALTER LOGIN login DISABLE

     ALTER LOGIN login WITH option [ ,... ] 

options:  

    PASSWORD = 'password' 

    [ 

      OLD_PASSWORD = 'oldpassword'| password_option[password_option] 

    ]

    DEFAULT_DATABASE = database

    DEFAULT_LANGUAGE = language

    NAME = login

    CHECK_POLICY = {ON | OFF}

    CHECK_EXPIRATION = {ON | OFF}

    CREDENTIAL = credential

    NO CREDENTIAL

Example

To enable the login

alter login sqlindex enable

To disable Login

alter login sqlindex disable

To change the password

alter login sqlindex with password='Sqlindex'

To change the default database of login.

alter login sqlindex with DEFAULT_DATABASE=[Dbsqlindex]

DROP LOGIN

Remove a SQL Server login account (User).

Syntax

drop login Sqlindex