Interview Question of the Week #056 – How to fix Installation Failure – Rule “Setup account privileges” Failed in SQL Server

Sometimes in the interviews I observe that users are able to answer all the theoretical questions correct but when it is about doing practical they fail. A while ago, when I was helping a large organization with interview, suddenly their DBA reported that one of their server has installation error. We found this as a great opportunity to test a new candidate and gave him problem to solve. He was successfully able to solve the problem.

Question: How will you fix the installation failure error setup account privileges error in SQL Server?

setup rule 01 Interview Question of the Week #056   How to fix Installation Failure – Rule Setup account privileges Failed in SQL Server

Answer:

When I clicked on “failed” hyperlink, here is the message.

setup rule 02 Interview Question of the Week #056   How to fix Installation Failure – Rule Setup account privileges Failed in SQL Server

Here is the text of the message.

—————————
Rule Check Result
—————————
Rule “Setup account privileges” failed.
The account that is running SQL Server Setup does not have one or all of the following rights: the right to back up files and directories, the right to manage auditing and the security log and the right to debug programs. To continue, use an account with both of these rights. For more information, see http://msdn.microsoft.com/en-us/library/ms813696.aspx, http://msdn.microsoft.com/en-us/library/ms813959.aspx and http://msdn.microsoft.com/en-us/library/ms813847.aspx.
—————————
OK
—————————

I always look at setup logs to see the exact issue. I opened “SystemConfigurationCheck_Report.htm” and below is the failure.

setup rule 03 Interview Question of the Week #056   How to fix Installation Failure – Rule Setup account privileges Failed in SQL Server

Then I looked into “Detail.txt” and searched for “HasSecurityBackupAndDebugPrivilegesCheck” as shown in above screenshot. Failure is listed below.

(09) 2016-01-28 19:58:11 Slp: Initializing rule      : Setup account privileges
(09) 2016-01-28 19:58:11 Slp: Rule is will be executed  : True
(09) 2016-01-28 19:58:11 Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.FacetPrivilegeCheck
(09) 2016-01-28 19:58:11 Slp: Rule ‘HasSecurityBackupAndDebugPrivilegesCheck’ Result: Running process has SeSecurity privilege, has SeBackup privilege and does not have SeDebug privilege.
(09) 2016-01-28 19:58:11 Slp: Evaluating rule        : HasSecurityBackupAndDebugPrivilegesCheck
(09) 2016-01-28 19:58:11 Slp: Rule running on machine: PINALVM1
(09) 2016-01-28 19:58:11 Slp: Rule evaluation done   : Failed

As highlighted above, the account which was running setup was missing second one.

SeSecurity: Manage auditing and the security log

SeDebug: Debug Programs

SeBackup: Back up files and directories

To see the “Local Security Policy”, we can go to Start > Run > SecPol.msc or open it from Administrative Tools.

setup rule 04 Interview Question of the Week #056   How to fix Installation Failure – Rule Setup account privileges Failed in SQL Server

“Debug programs” doesn’t have any user. By default, “Administrators” group is part of it which was removed by me during some hardening. Once I added default account, I have to restart the computer to get forward from that error.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #054 – Retrieve User Defined Object Details from sys.objects

Sometimes questions in the interview are extremely open ended and such questions can be very useful for candidate to play around with. For example, in one of the recent interview one of the interviewer asked the candidate that what is his favorite T-SQL script as a database administrator. The answer of the candidate was very simple, however, he was able to earn some brownie points with the help of his script.

Question: How do you retrieve user defined object details?

Answer: Here is a simple script which can help retrieve user defined object’s details with the help of sys.objects.

The sys.objects object catalog view contains a row for each user-defined, schema-scoped object that is created within a database. You can retrieve any user defined object details by querying sys.objects database.

Let us see one example of sys.objects database usage. You can run following query to retrieve all the information regarding name of the foreign key, name of the table it FK belongs and the schema owner name of the table.

USE AdventureWorks;
GO
SELECT name AS ObjectName,
OBJECT_NAME(schema_id) SchemaName,
OBJECT_NAME(parent_object_id) ParentObjectName, name, *
FROM sys.objects
WHERE TYPE = 'F'
GO

You can use any of the following in your WHERE clause and retrieve necessary information.

Object type:

AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
P = SQL stored procedure
PC = Assembly (CLR) stored procedure
FN = SQL scalar function
FS = Assembly (CLR) scalar function
FT = Assembly (CLR) table-valued function
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TR = SQL DML trigger
IF = SQL inline table-valued function
TF = SQL table-valued-function
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure
IT = Internal table

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #053 – What is the Difference Between Deterministic Functions and Nondeterministic Functions?

Some questions are so theoretical that I believe they really do not add too much value if users know that question or not. Here is one such question I am very confident that you agree with my point of view.

Questions: What is the Difference Between Deterministic Functions and Nondeterministic Functions?

Answer: 

Deterministic functions always return the same output result all the time it is executed for same input values. i.e. ABS, DATEDIFF, ISNULL etc.

Nondeterministic functions may return different results each time they are executed. i.e. NEWID, RAND, @@CPU_BUSY etc. Functions that call extended stored procedures are nondeterministic. User-defined functions that create side effects on the database are not recommended.

Now you have read the answer – I have a question back to you.

Did you the difference between deterministic and nondeterministic function before this blog? If no, has it ever impacted your performance in your daily job?

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #052 – Print String in Reverse Order

Last week, I attended the last interview of the year. One of my co-interviewer asked a coding question to the candidate. In this type of question, the candidate is given one hour with SQL Server with a help file. The computer does not have internet candidate can use all the help available from MSDN. In this case, the candidate was able to resolve the problem very successfully.

Question: Write a User Defined Function which can generate a script in reverse order without using the reverse function?

Answer: Here is the script for the user defined function which can generate script in the reversed order, (however, he failed at one condition where he used REVERSE function).

CREATE FUNCTION UDF_ReverseString
( @StringToReverse VARCHAR(8000),
@StartPosition INT)
RETURNS VARCHAR(8000)
AS
BEGIN
IF
(@StartPosition <= 0)
OR (
@StartPosition > LEN(@StringToReverse))
RETURN (REVERSE(@StringToReverse))
RETURN (STUFF (@StringToReverse,
@StartPosition,
LEN(@StringToReverse) - @StartPosition + 1,
REVERSE(SUBSTRING (@StringToReverse,
@StartPosition
LEN(@StringToReverse) - @StartPosition + 1))))
END
GO

What really impressed me was that it also allowed to specify from which position the string has to be reversed.

Reversing the string from third position
SELECT dbo.UDF_ReverseString('forward string',3)

Results Set : forgnirts draw

Reversing the entire string passing 0 as beginning character
SELECT dbo.UDF_ReverseString('forward string',0)

Results Set : gnirts drawrof

Reversing the entire string passing negative number as beginning character
SELECT dbo.UDF_ReverseString('forward string',-9)

Results Set : gnirts drawrof

Reversing the entire string passing larger number than string length as beginning character
SELECT dbo.UDF_ReverseString('forward string',900)

Results Set : gnirts drawrof

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #051 – Actual Execution Plan vs. Estimated Execution Plan

In one of the recent interview, I met quite a few interesting candidates. It is common for employees ask many questions during an interview, but there are times when candidates also ask questions back to the interviewee. I was asked following question recently.

Question: Do you use Actual Execution plan or Estimated Execution plan?

Answer: I always use the Actual Execution Plan as it is relatively accurate.

Why not Estimated Execution Plan? It is not accurate. Sometime it is easier or useful to to know the plan without running the query. I just run a query and have correct and accurate Execution Plan.

Shortcut for Display Estimated Execution Plan : CTRL + L
Shortcut for Include Actual Execution Plan : CTRL + M

You can use SSMS to include the execution plan as well.

ExecutionPlan SQL SERVER Actual Execution Plan vs. Estimated Execution Plan

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Interview Question of the Week #050 – Query to Retrieve Second Highest Salary of Employee

There are some questions which never gets old and I keep on seeing them again and again every time I participate in the interview to help hire qualified Developers and DBA. Here is another popular question which I often see.

Question: Write a query to retrieve Second (or Nth) highest salary of an employee from a table.

Answer: Here is the generic query for the same. You can use the following query for any other similar logic.

Replace Employee with your table name, and Salary with your column name. Where N is the level of Salary to be determined.

SELECT *
FROM Employee E1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(E2.Salary))
FROM Employee E2
WHERE E2.Salary > E1.Salary)

In the above example, the inner query uses a value of the outer query in its filter condition, meaning; the inner query cannot be evaluated before evaluating the outer query. So each row in the outer query is evaluated first and the inner query is run for that row.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Interview Question of the Week #049 – Taking Database Offline

Here is the conversation which I observed the other day.

Question: How do we take database off-line?

Answer: Well, we can run following script to take the database offline.

ALTER DATABASE [mydb] SET OFFLINE WITH
ROLLBACK IMMEDIATE

Question: Let us assume that your boss suggest that you should wait 30 seconds before taking the database offline, but no new processes should start during this 30 seconds what will be the command for the same?

Answer: We can easily change the time interval to take the database offline. Here is the script for the same.

ALTER DATABASE [mydb] SET OFFLINE WITH
ROLLBACK
AFTER 30 SECONDS

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #047 – Logic To Find Weekdays Between Two Dates

I often enjoy seeing interview questions where the contestant has to write SQL Scripts. Here is another such example.

Question: How to find weekdays between two dates?

Answer: Here is a function which can be used to find weekdays between two dates.

CREATE FUNCTION dbo.getDayCount(@startdate date, @enddate date)
RETURNS INT
AS
BEGIN
DECLARE
@count INT = 0
WHILE @startdate<=@enddate
BEGIN
IF
DATEPART(dw,@startdate) > 1 AND DATEPART(dw,@startdate) < 7
BEGIN
SET
@count = @count + 1
END
SET
@startdate = DATEADD(DAY,1,@startdate)
END
RETURN
@count
END
-- Execute Function
SELECT dbo.getDayCount ('10/10/2015', '11/10/2015')

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #045 – How to Do Case Sensitive Search?

One of my popular questions which helps me to understand lots of understanding of SQL by SQL developer.

Question: How doing a case sensitive search in SQL Server?

Answer:

If Column1 of Table1 has following values ‘CaseSearch, casesearch, CASESEARCH, CaSeSeArCh’, following statement will return you all the four records.

SELECT Column1
FROM Table1
WHERE Column1 = 'casesearch'

To make the query case sensitive and retrieve only one record (“casesearch”) from above query, the collation of the query needs to be changed as follows.
SELECT Column1
FROM Table1
WHERE Column1 COLLATE Latin1_General_CS_AS = 'casesearch'

Adding COLLATE Latin1_General_CS_AS makes the search case sensitive.

Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS is not case sensitive.

To change the collation of the any column for any table permanently run following query.
ALTER TABLE Table1
ALTER COLUMN Column1 VARCHAR(20)
COLLATE Latin1_General_CS_AS

To know the collation of the column for any table run following Stored Procedure.
EXEC sp_help DatabaseName

Second results set above script will return you collation of database DatabaseName.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Interview Question of the Week #044 – What is the difference of performance between SELECT and SET?

Question: What is the difference of performance between SELECT and SET?

Answer:

SELECT : Designed to return data.
SET : Designed to assign values to local variables.

While testing the performance of the following two scripts in query analyzer, interesting results are discovered.

SET @foo1 = 1;
SET @foo2 = 2;
SET @foo3 = 3;
SELECT
@foo1 = 1,
@foo2 = 2,
@foo3 = 3;

While comparing their performance in loop SELECT statement gives better performance then SET. In other words, SET is slower than SELECT. The reason is that each SET statement runs individually and updates on values per execution, whereas the entire SELECT statement runs once and update all three values in one execution.

SET is the ANSI standard for variable assignment, SELECT is not. SET can only assign one variable at a time, SELECT can make multiple assignments at once – that gives SELECT slight speed advantage over SET. If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise an error. SELECT will assign one of the values to the variable and hide the fact that multiple values were returned. When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all keeping the variable unchanged.

Reference : Pinal Dave (http://blog.SQLAuthority.com)