Posted in Development

SQL Server – Check the status of active Trace Flags

Hi friends, few days back I was exploring data masking features introduced in SQL Server 2016. I got stuck in some issue and needs to check the information of the trace flags that I enabled. I was searching if there is any system table exists for it or not then I found a DBCC command to check the status about the trace flags which are currently active. So, I thought of writing blog on it so it might help others also in future.

Management command to check for this is –

DBCC TRACESTATUS

It is used to display the status all the trace flags

Syntax from bol

DBCC TRACESTATUS ( [ [ trace# [ ,…n ] ] [ , ] [ -1 ] ] )

[ WITH NO_INFOMSGS ]

Currently there is not active trace flag so let me enabled one for demo –

DBCC TRACEON(1222)

1222 trace flag returns locks and resource that are participating during deadlocks.

As, I have enabled this trace flag now let’s check the status of it using Trace Status command –

DBCC TRACESTATUS(1222)

After executing the above it gives the following output in a result set –

TraceFlag_Status

TraceFlag – Trace Flag number

Status – Status of trace flag (1- Enabled)

Global – Whether trace flag is set globally or not (1 – TRUE, 0 – FALSE).

Session – Whether trace flag is set for session or not (1 – TRUE, 0 – FALSE).

So friends, in this way we can check the status of the trace flag. Have a nice day!!

Advertisements
Posted in Development

SQL Server – Trace flag changes in Dynamic data masking in SQL Server 2016 CTP 3.0

Hi friends,

In new CTP version SQL server 2016 which is 3.0, I was trying to explore the new masking function Random and following the same steps to apply masking which I used in previous CTP version 2.0.

I found that some changes has been made in Trace flag area and come to conclusion that in earlier CTP versions CTP 2.0 we need to enable the trace flags 209 and 219 while in CTP 2.1+ versions we do not required to enable these trace flags otherwise it will gives error –

Incorrect syntax near ‘masked’.

Let me show you a demo on this –

SQL Server 2016 CTP 2.0

If we try to apply masking on a column then below query without enabling trace flag gives error –

ALTER TABLE Demo_Masking

ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = ‘random(1,90000)’);

T2

So to remove this error we need to enable the Trace flag 209 and 219 and rewrite the query like this –

DBCC TRACEON (209,219,-1)

GO

ALTER TABLE Demo_Masking

ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = ‘random(1,90000)’);

It will get executed successfully.

SQL Server 2016 CTP 3.0

In this CTP version if we want to apply masking on a column then enabling trace flag is not required. If trace flag is enabled then it will give error ‘Incorrect syntax near ‘masked’’.

I have executed a query after enabling trace flags and it gives error –

DBCC TRACEON (209,219,-1)

ALTER TABLE Demo_Masking

ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = ‘random(1,90000)’);

T1

So first disabled the flag and just execute the ALTER statement only.

DBCC TRACEOFF(209,219,-1)

ALTER TABLE Demo_Masking

ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = ‘random(1,90000)’);

It gets executed successfully.

So friends, if you are using latest CTP version of SQL 2016 then we don’t need to enable trace flags 209 and 219.

Hope it will helps you. Have a happy learning.

Posted in Uncategorized

SQL SERVER – Random function in Dynamic Data Masking in SQL Server 2016 CTP 3

Hi friends,

With new CTP version of SQL Server 2016 many new functionality are added and modified. As with introduction SQL 2016 new functionality Dynamic data masking get introduced which helps in protecting the confidential data from users who do not have access to view it. In earlier CTP version data masking were of three types:

  1. Default
  2. Custom
  3. Email

I have already written about these in my earlier blogs here :

http://kapilsqlgeek.com/2015/07/26/sql-server-2016-blogs/

In CTP 3.0 of SQL 2016 one more type gets added with name Random.

Random function can be used for any numeric datatype to mask the value with any random number defined within a specified range.

Random ([start range], [end range])

In this blog I will give you the overview of new Random function with an example.

Let’s first create a table Demo_Masking with some data in it.

Create table Demo_Masking

(

EmployeeId INT,

FirstName varchar(10),

LastName varchar(20),

Salary INT

)

GO

—-INSERT dummy data into Demo_masking table

INSERT INTO Demo_Masking (EmployeeId, FirstName, LastName, Salary)

SELECT 11, ‘Kapil’, ‘Singh’, 50000

UNION ALL

SELECT 12, ‘Ankit’,’Sharma’,35000

UNION ALL

SELECT 13, ‘Aditya’, ‘Sinha’, 45000

M1

Here, I have created an employee with some columns, but I don’t want user to see the salary of other users so I will mask it with any random numbers.

Now, I am altering the table Demo_masking and adding masking function on columns Salary using this syntax:

ALTER TABLE Demo_Masking

ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = ‘random(1,90000)’);

After applying masking, I will create a user ‘Masking’ which will have only read access (SELECT) on table Demo_Masking.

CREATE USER Masking WITHOUT LOGIN

GRANT SELECT ON Demo_Masking to Masking

Now let me execute below query –

SELECT * FROM Demo_Masking

GO

EXECUTE AS USER = ‘Masking’

SELECT * FROM Demo_Masking

REVERT

M2

After executing queries with current user and new user which has only SELECT rights we see that the salary column is showing random values for it and restricting the user to see the original salary of other users. In this way we can use random function to mask the data with any random numbers.

Hope you will like this post.