what is the default setting for identity_insert in sql database?

Default setting depends on how you defined the identity column when you created the table for example

CREATE TABLE Table_Name(ID INT IDENTITY(1,1))
GO

this ID column will have a Seed value of 1 and increment by 1.

By default you cannot add values to Identity column but you can change this default behaviour by executing the following statement.

SET IDENTITY_INSERT Table_Name ON;

Once you have inserted the values then you can set it back to its default behaviour by executing the following statement.

SET IDENTITY_INSERT Table_Name OFF;

even though we can pass values to Identity column but its not a good practice, since it is an auto generated number, if you add the values into identity column yourself and then Identity column generates the same number later on you can have duplicates in your identity column.

so its best to leave identity column alone, and let it generate the values for you.

if it is necessary to add values to Identity column then I would recommend executing the following statement after every you have Indet_Insert ON and add some values to Identity column,

DBCC CHECKIDENT ( table_name, RESEED,  0) 
DBCC CHECKIDENT ( table_name, RESEED) 

The 1st statement will RESEED the value of Identity column to the smallest values in your identity column,

2nd RESEED statement with out any Seed value provided, will reseed the Identity value to the next available value to the highest value in your identity column.

In case of someone searching for a way to create a key without identity column:

CREATE TABLE [dbo].[TableName](
    [Id] [int] NOT NULL PRIMARY KEY
)

instead of

CREATE TABLE [dbo].[TableName](
    [Id] [int] IDENTITY(1,1) NOT NULL
)