Applying database principal through out the server (for all databases) for a particular user

Ever come across a requirement which you required to give db_datareader access to a specific user across all the databases on a particular SQL Server. The task is simple as long as you don’t have many databases in the same SQL Server. How ever if the number of databases are very high this can be a very time consuming one.

This can be done either using the GUI (SSMS) or using a T-SQL script. We will consider both options.

Using SQL Server Management Studio

In order to illustrate this we will create a SQL Login ‘db_user_read_only’ with ‘public’ server role and on the user mapping, we will apply the db_datareader principal.

Like mentioned it would be easy to use the GUI when you have less number of databases. But if the SQL Server contains lots of databases this will be a very time consuming job. Then it would be very handy to use the latter approach.

Using T-SQL

You can use the following script to apply the db_datareader principal across all the databases on a particular server.

DECLARE 
	@Sql AS NVARCHAR(MAX)
	,@UserId AS VARCHAR(MAX) = 'YourLoginId'
SET @Sql = CONCAT('
USE [?];
IF EXISTS (SELECT 0 FROM sys.database_principals AS DP WHERE name = ''',@UserId,''')
BEGIN
	EXEC sys.sp_change_users_login ''update_one'',''',@UserId,''',''',@UserId,'''
END
ELSE
	
	CREATE USER [',@UserId,'] FOR LOGIN [',@UserId,']
	ALTER ROLE [db_datareader] ADD MEMBER [',@UserId,']
')
EXEC sys.sp_MSforeachdb 
	@command1 = @Sql
	,@replacechar = '?'

Please note the following:

• On the above code I haven’t excluded the system databases.
• If the login exists on the database it will map the database user using sp_change_users_login

Hope this might be very useful to you.

Replacing sp_depends with sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities

sp_depends have been one of the most used system stored procedures in SQL Server. Infact many of us still use that even though Microsoft had annouced that it will be removed from the future releases.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-depends-transact-sql?view=sql-server-2017

Alternatively Microsoft has provided two dynamic management views (these have been introduced with SQL Server 2008) in order to get similar kind of information.

• sys.dm_sql_referencing_entities
• sys.dm_sql_referenced_entities

You can get further details on the aforementioned view by visiting the link. (links are embedded into the view name)
However if you have used sp_depends you might have already faced the issue that the results which is being returned from this stored procedure is not very accurate (most of the time it seems fine)
Otherday I was going through these two view in order to create an sp which is similar to sp_depends and thought of sharing the query so that it can be useful to anyone who depends on this sp.

DECLARE
 @objname   AS NVARCHAR(100) = 'Website.SearchForPeople'
 ,@objclass   AS NVARCHAR (60) = 'OBJECT'


  SELECT 
   CONCAT(sch.[name],'.',Obj.[name]) AS [name]
   ,(CASE Obj.type
    WHEN 'C'  THEN 'CHECK constraint'
    WHEN 'D'  THEN 'DEFAULT (constraint or stand-alone)'
    WHEN 'F'  THEN 'FOREIGN KEY constraint'
    WHEN 'PK' THEN 'PRIMARY KEY constraint'
    WHEN 'R'  THEN 'Rule (old-style, stand-alone)'
    WHEN 'TA' THEN 'Assembly (CLR-integration) trigger'
    WHEN 'TR' THEN 'SQL trigger'
    WHEN 'UQ' THEN 'UNIQUE constraint'
    WHEN 'AF' THEN 'Aggregate function (CLR)'
    WHEN 'C' THEN 'CHECK constraint'
    WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)'
    WHEN 'F' THEN 'FOREIGN KEY constraint'
    WHEN 'FN' THEN 'SQL scalar function'
    WHEN 'FS' THEN 'Assembly (CLR) scalar-function'
    WHEN 'FT' THEN 'Assembly (CLR) table-valued function'
    WHEN 'IF' THEN 'SQL inline table-valued function'
    WHEN 'IT' THEN 'Internal table'
    WHEN 'P' THEN 'SQL Stored Procedure'
    WHEN 'PC' THEN 'Assembly (CLR) stored-procedure'
    WHEN 'PG' THEN 'Plan guide'
    WHEN 'PK' THEN 'PRIMARY KEY constraint'
    WHEN 'R' THEN 'Rule (old-style, stand-alone)'
    WHEN 'RF' THEN 'Replication-filter-procedure'
    WHEN 'S' THEN 'System base TABLE'
    WHEN 'SN' THEN 'Synonym'
    WHEN 'SO' THEN 'Sequence OBJECT'
    WHEN 'U' THEN 'Table (user-defined)'
    WHEN 'V' THEN 'VIEW'
    WHEN 'SQ' THEN 'Service queue'
    WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
    WHEN 'TF' THEN 'SQL table-valued-function'
    WHEN 'TR' THEN 'SQL DML trigger'
    WHEN 'TT' THEN 'Table type'
    WHEN 'UQ' THEN 'UNIQUE CONSTRAINT'
    WHEN 'X'  THEN 'Extended stored procedure'
    ELSE 'Undefined'
   END) AS [type]
   ,Obj.create_date
   ,Obj.modify_date
   ,src.referenced_minor_name AS [column]
   ,IIF(src.is_selected   = 1,'yes','no') AS is_selected
   ,IIF(src.is_updated    = 1,'yes','no') AS is_updated
   ,IIF(src.is_select_all = 1,'yes','no') AS is_select_all
   ,IIF(src.is_insert_all = 1,'yes','no') AS is_insert_all
  FROM 
   sys.dm_sql_referenced_entities (@objname,@objclass) AS src
   JOIN sys.objects AS Obj
    ON src.referenced_id = Obj.[object_id]
   JOIN sys.schemas AS Sch
    ON Sch.[schema_id] = Obj.[schema_id]
  WHERE 1=1
  
  SELECT 
   CONCAT(Src.referencing_schema_name,'.',Src.referencing_entity_name) AS [name]
   ,(CASE Obj.type
    WHEN 'C'  THEN 'CHECK constraint'
    WHEN 'D'  THEN 'DEFAULT (constraint or stand-alone)'
    WHEN 'F'  THEN 'FOREIGN KEY constraint'
    WHEN 'PK' THEN 'PRIMARY KEY constraint'
    WHEN 'R'  THEN 'Rule (old-style, stand-alone)'
    WHEN 'TA' THEN 'Assembly (CLR-integration) trigger'
    WHEN 'TR' THEN 'SQL trigger'
    WHEN 'UQ' THEN 'UNIQUE constraint'
    WHEN 'AF' THEN 'Aggregate function (CLR)'
    WHEN 'C' THEN 'CHECK constraint'
    WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)'
    WHEN 'F' THEN 'FOREIGN KEY constraint'
    WHEN 'FN' THEN 'SQL scalar function'
    WHEN 'FS' THEN 'Assembly (CLR) scalar-function'
    WHEN 'FT' THEN 'Assembly (CLR) table-valued function'
    WHEN 'IF' THEN 'SQL inline table-valued function'
    WHEN 'IT' THEN 'Internal table'
    WHEN 'P' THEN 'SQL Stored Procedure'
    WHEN 'PC' THEN 'Assembly (CLR) stored-procedure'
    WHEN 'PG' THEN 'Plan guide'
    WHEN 'PK' THEN 'PRIMARY KEY constraint'
    WHEN 'R' THEN 'Rule (old-style, stand-alone)'
    WHEN 'RF' THEN 'Replication-filter-procedure'
    WHEN 'S' THEN 'System base TABLE'
    WHEN 'SN' THEN 'Synonym'
    WHEN 'SO' THEN 'Sequence OBJECT'
    WHEN 'U' THEN 'Table (user-defined)'
    WHEN 'V' THEN 'VIEW'
    WHEN 'SQ' THEN 'Service queue'
    WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
    WHEN 'TF' THEN 'SQL table-valued-function'
    WHEN 'TR' THEN 'SQL DML trigger'
    WHEN 'TT' THEN 'Table type'
    WHEN 'UQ' THEN 'UNIQUE CONSTRAINT'
    WHEN 'X'  THEN 'Extended stored procedure'
    ELSE 'Undefined'
   END) AS [type]
   ,Obj.create_date
   ,Obj.modify_date
  FROM 
   sys.dm_sql_referencing_entities (@objname,@objclass) AS Src
   JOIN sys.objects AS Obj
    ON Obj.[object_id] = Src.referencing_id 

I have even compiled a stored procedure using this syntax and it can be found on the following reporsitory: https://github.com/manjukefernando/sp_depends_v2

Computed columns in SQL Server

Computed columns are type of columns which the values are derived based on one or more other columns. Hence the data type on the computed column depends on the result of the derived column values.

Computed columns is a feature which has been there in SQL Server since version 2000. But in my experience I feel that it has been a feature which's been used less compared to many other features available, and during discussions and interviews this is something which most developers slips or fails to answer.

Why do we need computed columns ?

First we will consider a case where we need to store details on a table without the usage of computed columns.
Consider we have a table which contains employee details. We have two columns to store employee’s first and last names. But we also required to have a column which we need to store their full name as well by concatenating the first and last names. So the correct way is to have the third column which contains the full name and the data needs to be inserted during the employee record is created and it should be maintained in the case where the details are updated as well. Otherwise the data integrity will be lost. (One might debate that the full name can be built from the business logic code using the first and last names. But for the illustration purpose we would consider that we are maintaining it using SQL Server)

CREATE TABLE dbo.Employee(
    Id     INT 
    ,FirstName    VARCHAR(30)
    ,LastName    VARCHAR(30)
    ,FullName    VARCHAR(61)
)

How ever we could achieve the same with the use of a computed column and with a less effort compared to the first approach.

CREATE TABLE dbo.Employee(
    Id     INT 
    ,FirstName    VARCHAR(30)
    ,LastName    VARCHAR(30)
    ,FullName AS CONCAT(FirstName,' ',LastName)
)

Let’s insert few records to the table which we created now.

INSERT INTO dbo.Employee(Id, FirstName, LastName) 
VALUES (1,’John’,’Doe'),(2,’Jane’,’Doe')

PERSISTED, DETERMINISTIC or NON-DETERMINISTIC ?

The values reflected on computed column can be either deterministic or persisted.

When the values are deterministic or non-deterministic the value in the column will not be saved on to the table physically. Instead it always calculated during the query execution. Hence the value could differ based on the functions you use in the formula. E.g: If you use GETDATE() in the calculated column, it will always return a different value during each execution.

CREATE TABLE dbo.Employee2(
    Id     INT 
    ,FirstName    VARCHAR(30)
    ,LastName    VARCHAR(30)
    ,CreatedDate AS GETDATE()
)

INSERT INTO dbo.Employee2(Id, FirstName, LastName) VALUES 
    (1,'John','Doe') 

And when queried the calculated column returns different values as shown below.

**Note: The above mentioned can be achieved using a default constraint as well. I have used that example on strictly illustration basis.

You can further read on deterministic and non-deterministic function on the following Microsoft documentation.
https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-2017

Computed column values can be persisted by adding the keyword PERSISTED when the column is created using T-SQL or by the table designer in SSMS.
We will drop ‘FullName’ column and recreate the column.

ALTER TABLE dbo.Employee DROP COLUMN FullName;
ALTER TABLE dbo.Employee 
 ADD FullName AS CONCAT(FirstName,' ',LastName) PERSISTED;

**Note: If you try to drop the ‘CreatedDate’ column on Employee2 and try to create it as PERSISTED, it will throw an error. Because computed columns can only be persisted when it’s deterministic.
Msg 4936, Level 16, State 1, Line 45
Computed column 'CreatedDate' in table 'Employee2' cannot be persisted because the column is non-deterministic.
Now when the expression is evaluated during the execution, the ‘FullName’ will be saved into the table.
The data is read-only to the developer and it’s maintained by the engine. When the data is changed on the columns which was used in the formula, the computed values will be changed.

Data Encryption in SQL Server using T-SQL Functions (ENCRYPTBYPASSPHRASE, DECRYPTBYPASSPHRASE & HASHBYTES)

Decade ago data was just an entity which helped business to operate smoothly. By then data was considered as some sort of business related information just stored in a database, which can be retrieved based on the demand/requirement as per the demand. E.g: a bunch of products, transactions such as invoices, receipts etc. or customer details.

But today data has become an important entity, which drives business towards success. In today’s fast-moving world, companies who owned data and does analytics has become the most successful companies.

However one of the major concerns we have today is how to protect these data. Especially the sensitive ones. Since more data is being exposed to the cloud, it’s essential to protect it from going to the wrong hands and it has become a major problem since hackers nowadays are well equipped and are always on the look for stealing this valuable information whenever possible, since it’ll be a valuable asset in the open market.

But protecting the data from unauthorized access is a must. Failing to do so can have unexpected consequences. Entire business could get wiped out of the business due to this. Hence enterprises should seriously consider protecting their data and we will discuss how we can achieve this in SQL Server through data encryption.

Ways of Data Encryption in SQL Server

There are few ways of encrypting data in SQL Server. We will discuss the advantages and disadvantages of each method.

SQL Server provides following methods to encrypt data:

• T-SQL Functions
• Using Symmetric Keys**
• Using Asymmetric Keys**
• Using Certificates**
• Transparent Data Encryption**

**Note : In this article I only plan to explain encryption/decryption functionality using T-SQL. I will talk about other methods which is mentioned about in future articles.

Using T-SQL Functions

Encrypting data using ENCRYPTBYPASSPHRASE 

Encryption is done using T-SQL function ENCRYPTBYPASSPHRASE.

ENCRYPTBYPASSPHRASE(passphrase,text_to_encrypt)

The first parameter is the passphrase which can be any text or a variable of type NVARCHAR, CHAR, VARCHAR, BINARY, VARBINARY, or NCHAR. The function uses this passphrase to generate a symmetric key.

For the illustration purpose we will create a table which to hold employee details

CREATE TABLE dbo.Employee(
    Id   			 INT
    ,EmpName   	 VARCHAR(100)
    ,EmpDOB   		 SMALLDATETIME
    ,SSN   		 VARBINARY(128)
)

This example is to demonstrate the data encryption during INSERT DML Statement

INSERT INTO dbo.Employee(
	Id
	,EmpName
	,EmpDOB
	,SSN
)
VALUES(
	1
	,'Luke'
	,'01-June-1980'
	,ENCRYPTBYPASSPHRASE('Pa$$W0rd4EnCRyPt10n','111-22-3333')
) 

Further details can be found in the Microsoft Documentation:
https://docs.microsoft.com/en-us/sql/t-sql/functions/encryptbypassphrase-transact-sql

Decrypting data using T-SQL function DECRYPTBYPASSPHRASE 

Will take the same details which we inserted during the first case. The encrypted data can be decrypted using SQL function DECRYPTBYPASSPHRASE. If any attempt has been made to decrypt the data without using DECRYPTBYPASSPHRASE nor providing the proper details, it will fail the operation.

Without Decryption

SELECT 
	Id,EmpName,EmpDOB,CONVERT(VARCHAR(128),SSN) AS SSN
FROM 
	dbo.Employee
WHERE 
	Id = 1

With Decryption (Incorrect Pass-phrase)

SELECT 
	Id
	,EmpName
	,EmpDOB
	,DECRYPTBYPASSPHRASE('IncorrectPassword',SSN ) AS SSN 
FROM 
	dbo.Employee
WHERE 
	Id = 1

But providing the correct pass-phrase will return the correct details

SELECT 
	Id
	,EmpName
	,EmpDOB
	,CONVERT(VARCHAR(128),DECRYPTBYPASSPHRASE('Pa$$W0rd4EnCRyPt10n',SSN )) AS SSN
FROM 
	dbo.Employee
WHERE 
	Id = 1

However there could be a requirement which you need to protect your data, not from stealing, but from getting updated with someone else’s.

One classic example is a login table. Suppose we have a table which stores login credentials, which is having the following structure.

*Note: In real world cases, usually it’s more secure if you hash passwords rather than encrypting them. But I am using encryption for illustration purpose.

So if a person has access to update the details on the password column, he/she can easily replace the contents with their own and log on using that. This can be stopped by providing two additional values when details are inserted to the table using ENCRYPTPASSPHRASE.

CREATE TABLE dbo.LoginCredentails(
	UserId		INT
	,UserName	VARCHAR(20)
	,Pwd		VARBINARY(128)
)

We will insert two records to the above created table.

INSERT INTO dbo.LoginCredentails(
	UserId
	,UserName
	,Pwd
)
VALUES
	(1001,'luke.skywalker',ENCRYPTBYPASSPHRASE('Pa$$W0rd4EnCRyPt10n','force be with you',1,CAST(1001 AS sysname)))
	,(1002,'darth.vader',ENCRYPTBYPASSPHRASE('Pa$$W0rd4EnCRyPt10n','i am your father',1,CAST(1002 AS sysname)))

Please note that unlike the previous example, we are now providing two additional values to the ENCRYPTBYPASSPHRASE function. The first values is 1, which indicates whether whether an authenticator will be encrypted together with the password. If the value is 1 and authenticator will be added. The second value is the data which from which to derive an authenticator. In this example we will use a value similar to a user id, so that when the value is decrypted, we could use the same value.

Following is a function to fetch the decrypted password based on the UserId. Assume we will be using this when validating the credential prior login.

CREATE FUNCTION  Fn_GetUserPwdById(@UserId AS INT)
RETURNS VARCHAR(50)
AS
BEGIN	

	DECLARE @Pwd AS VARCHAR(50)
	SELECT
		@Pwd = CONVERT(VARCHAR(50),DECRYPTBYPASSPHRASE('Pa$$W0rd4EnCRyPt10n',LC.Pwd,1,CAST(LC.UserId AS sysname))) 
	FROM
		dbo.LoginCredentails AS LC
	WHERE
		LC.UserId = @UserId

	RETURN @Pwd
END

Using the aforementioned function we will retrieve the details.

SELECT 
	UserId
	,UserName
	,dbo.Fn_GetUserPwdById(UserId) AS Pwd 
FROM
	dbo.LoginCredentails

But querying the data simply will get you the binary string of the encrypted value.

SELECT 
	UserId
	,UserName
	,Pwd 
FROM
	dbo.LoginCredentails

Suppose if a person has enough privileges to do an update the password with a known one (from an existing user) it’ll allow him/her to login to the system impersonating any user.

UPDATE LC SET LC.Pwd = (
	SELECT LC2.Pwd FROM dbo.LoginCredentails AS LC2 
	WHERE LC2.UserName = 'luke.skywalker'
) 
FROM dbo.LoginCredentails AS LC
WHERE
LC.UserName = 'darth.vader'

But if when the same function is used for decryption, it will return NULL for the updated record, preventing the login to be invalid if it’s replaced using an existing one.

Hashing data using HASBYTES

Apart from the above mentioned function, there’s another function which can be used to hash data. Unlike encrypting, there’s no way you can reverse the hashed data and see the raw details.

Syntax:

HASHBYTES ( 'algorithm', { @input | 'input' } )  
/*
algorithm::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512   
*/

There are two parameters which you require to provide. The first parameter is the algorithm which should be used for hashing. The hashing algorithm can be any of the following:

• MD2
• MD4
• MD5
• SHA
• SHA1
• SHA2_256
• SHA2_512

The second parameter is the input, which needs to be hashed. This can be either a character or binary string.

The return value is VARBINARY(n). n = maximum 8000 bytes.

Example:

DECLARE 
	@TextToHash AS NVARCHAR(1000) = N'My Secret Message'

SELECT HASHBYTES('SHA1',@TextToHash) AS HashedData

Further details can be found in the Microsoft Documentation:
https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql

Hope this might be useful to you and please feel free to comment your ideas.

Strange behaviour converting NVARCHAR(MAX) to BINARY

Few days back I was writing a CLR function to be used for hashing string values. The only option was the CLR functions since T-SQL doesn’t have any functionality to convert a string to hashed value using a key. Using the HASHBYTES function you can only provide the algorithm.

DECLARE @Data NVARCHAR(4000);  
SET @Data = CONVERT(NVARCHAR(4000),'My Secret Message');  
SELECT HASHBYTES('SHA1', @Data);  

I have written the CLR function to achieve the requirement, but during testing the validation was failing and when I go through the code I couldn’t find any issue in the function as well. But inspecting carefully I noticed that when a variable type NVARCHAR(n) and a variable type of NVARCHAR(MAX) gives different results when it’s converted to Binary. Which was the root cause for the issue I was facing.

DECLARE 
	@Data1	AS NVARCHAR(MAX) = '1111'
	,@Data2	AS NVARCHAR(10) = '1111'

SELECT 
	CAST(@Data1 AS BINARY(30)) AS ValueMax
SELECT 
	CAST(@Data2 AS BINARY(30)) AS ValueN

As you can see the above example the zero bytes are represented differently for NVARCHAR(MAX) when it’s converted to BINARY.

I do not have any explanation for this. I am sharing the information in case anyone come across this issue. Please feel free to comment.