Thursday, July 5, 2018

About SQL Server Engine & Agent Service Accounts


Configure Service Accounts for all SQL Server services.

Microsoft recommends creating individual accounts for all services. If you use the same service account for every server, then when somebody repeatedly mistypes the service account password during an installation, you can lock out the service account, causing widespread havoc.

Do not make the service account a local administrator. As a best practices SQL Server service should be using a minimally privileged account. We should always avoid running SQL Server services under the contexts of the local system, local administrator or domain administrator accounts.

If you're installing multiple components, the recommendation is to use separate accounts. Example you have two services to be concerned with: the main SQL Server service and the SQL Server Agent service. Even in this case it's recommended to use separate accounts.
We need to make sure that the SQL Server service account has “full control” permissions on data, log and backup directories for read and write activities so need to grant the ‘Perform Volume Maintenance Tasks’ right to the account that will be used for the SQL Server service.

We need to use SQL Server Configuration manager to do any modifications for SQL Services. Account change and Password change both should be done only via configuration manager not services.msc.  If we update/change service account details through SQL Server configuration manager, it will perform additional configurations, such as  updating the Windows local security store which protects the service master key for the Database Engine etc.

Windows Domain Account Permissions

SQL DATABASE Engine Service account should have below permissions
·         Log on as a serive permissions
·         Replace a process-level token
·         Bypass traverse checking
·         Permission to start SQL Writer
·         Permission to read the Event Log service
·         Permission to read the Remote Procedure Call service

SQL AGENT Service account should have below permissions
·         Log on as a serive permissions
·         Replace a process-level token
·         Bypass traverse checking
·         Adjust memory quotas for a process

Wednesday, June 13, 2018

Unable to ALTER SQL Server database from single user to multi user

When i try to alter database from single user to multi user i got facing below error

Transaction (Process ID 233) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205)

Solution :



USE [MyDatabase]
GO
SET DEADLOCK_PRIORITY LOW
GO
ALTER DATABASE MyDatabase SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

Drop DEFAULT Constraint in SQL Server


Syntax to drop existing default constraint from a table

USE [DatabaseName]

GO

ALTER TABLE [Table_Name]
DROP CONSTRAINT [DefaultConstraintName]


Below is the example to drop default constrain from orderqty table


USE [MyDatabase]

GO

ALTER TABLE [dbo].[orderqty]
DROP CONSTRAINT [CK__orderqty__qty__628FA481]


Add Default constraint for existing table in SQL Server

Below is the syntax for adding default constraint for an existing table



ALTER TABLE tbl_Name
                       ADD CONSTRAINT default_columnName
                                         DEFAULT value FOR columnNmae




example :
Below is the query to create DEFAULT constraint on qty column when the orderqty table already created



ALTER TABLE orderqty
ADD CONSTRAINT default_qty
DEFAULT 1 FOR qty;


We created default constraint and we did not inserted value for qty field using INSERT statement in below example but "1" value inserted for qty


Cannot alter the user 'dbo'. (Microsoft SQL Server, Error: 15150)


Problem

Cannot drop the user 'dbo'.

Rename failed for User 'dbo'.  (Microsoft.SqlServer.Smo)

Cannot alter the user 'dbo'. (Microsoft SQL Server, Error: 15150)





Solution 

USE My_Database; 
ALTER AUTHORIZATION ON My_Database::DATABASE_NAME TO sa

Tuesday, August 8, 2017

SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).


If SQL Server agent not started and if you find below error in event viewer

Error Details

 Log Name             :         Application
Source                  :         SQLSERVERAGENT
Date                      :         8/8/2017 3:49:43 PM
Event ID               :         103
Task Category      :         Service Control
Level                    :         Error
Keywords             :      Classic
User                      :          N/A
Computer             :      VmSQLServer
Description           :
SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).















Solution 

Provide instance in  in "ServerHost" under

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\SQLServerAgent

Goto run
type regedit
expand HKEY_LOCAL_MACHINE --> SOFTWARE --> Microsoft --> Microsoft SQL Server
click on SQLServerAgent in Microsoft SQL Server
In right side pane double click on ServerHost and provide instance name under  value data

Friday, June 9, 2017

Read only access to all databases for existing Login






SET NOCOUNT ON;

DECLARE @user_name    SYSNAME
        , @login_name SYSNAME;

SELECT @user_name = 'Domain\Login',
       @login_name = 'Domain\Login'

Select  '
    USE ' + QUOTENAME(NAME) + ';

    CREATE USER ' + QUOTENAME(@user_name)
       + ' FOR LOGIN ' + QUOTENAME(@login_name)
       + ' WITH DEFAULT_SCHEMA=[dbo];

    EXEC sys.sp_addrolemember
      ''db_datareader'',
      ''' + @user_name+ ''';

   
GO
'
FROM   sys.databases
WHERE  database_id > 4
       AND state_desc = 'ONLINE'