Feeds:
Posts
Comments

Archive for the ‘SQL SERVER’ Category

Problem :
There is a common problem in SQL Server, when you give access of any table to any USER then the USER can view the entire table data and after giving access to the table if you would like to restrict this user NOT to view some critical data in that table, out of the box it is not possible in SQL Server, however there are some workarounds but those workarounds can make your life difficult as DBA. The GOOD NEWS is that it is POSSIBLE in SQL Server 2016 as out of the box solution and the name of this feature is Row Level Security (RLS). In this article, I will demonstrate how to implement RLS in your database environment step by step.

Before proceeding to the solution, I would like to create a sample.

Sample :
I will create two branches (Branch A & B) data in the sample table and later on restrict branch Administrator to view their own branch data and ONLY db_owner (or you can select any other role to give full access to the above table) can view all the data in the table.

CREATE DATABASE Sample_DB
GO
USE Sample_DB
GO
--DROP TABLE tbl_Sample
GO
CREATE TABLE tbl_Sample
(
 [ID] int,
 [Name] varchar(50),
 [BranchName] varchar(50)
)
GO
INSERT INTO tbl_Sample VALUES (1,'Ken','Branch A')
INSERT INTO tbl_Sample VALUES (2,'Selena','Branch A')
INSERT INTO tbl_Sample VALUES (3,'Mae','Branch B')
INSERT INTO tbl_Sample VALUES (4,'Tom','Branch B')
INSERT INTO tbl_Sample VALUES (5,'Thomas','Branch B')
GO
SELECT * FROM tbl_Sample
GO

Row Level Security (RLS) 1.1

Solution :

Step 1 :
Let me create the Branch A and Branch B administrator role in the respective databases. Given below is the script.

USE Sample_DB
GO
EXEC sp_addrole 'Branch A Admin'
GO
EXEC sp_addrole 'Branch B Admin'
GO

Row Level Security (RLS) 1.2

Step 2 :
In the above step, I created the role for branches A & B, now let me create two users for respective branches, who can view only respective branch data in the table. Given below is the script.

USE Sample_DB
GO
CREATE USER USER_A WITHOUT LOGIN; -- Branch A Admin
GO
CREATE USER USER_B WITHOUT LOGIN; -- Branch B Admin
GO

Row Level Security (RLS) 1.3

Step 3 :
Lets add User A & B (created in step 2) as a member in the respective roles (created in step 1). Given below is the script.

USE Sample_DB
GO
EXEC sp_addrolemember 'Branch A Admin', 'USER_A'
GO
EXEC sp_addrolemember 'Branch B Admin', 'USER_B'
GO

Step 4:
Grant the SELECT access of table (tbl_Sample) to the users (User_A & User_B). After granting the Select access they (USER_A, USER_B) can view the entire data in the table (tbl_Sample). Given below is the script.

USE Sample_DB
GO
GRANT SELECT ON tbl_Sample TO USER_A;
GO
GRANT SELECT ON tbl_Sample TO USER_B;
GO

Step 5:
Lets find out the member principal ID. This is a unique ID corresponding to each member of the role. Using member principal ID, we will allow users to view the respective data ONLY in the table, later in the article. Given below is the script.

USE Sample_DB
GO
SELECT role_principal_id,
USER_NAME(role_principal_id) As [Role],
member_principal_id,
USER_NAME(member_principal_id) As [Member]
FROM sys.database_role_members
WHERE USER_NAME(role_principal_id)
IN ('Branch A Admin','Branch B Admin')
GO

Row Level Security (RLS) 1.4

Step 6:
Lets create a user defined table where we must enter the receptive Branch Name and member Principal ID which we found in the previous step (Step 5). Actually, now we are trying to assign each branch access to respective users using Database Principal ID (Member Principal ID). Given below is the script.

USE Sample_DB
GO
DROP TABLE dbo.tbl_user
GO
CREATE TABLE dbo.tbl_user
(
 [ID] int,
 [Name] varchar(50),
 [BranchName] varchar(50),
 [DatabasePrincipalID] int
)
GO
INSERT INTO dbo.tbl_user VALUES (1,'USER A','Branch A',7)
INSERT INTO dbo.tbl_user VALUES (2,'USER B','Branch B',8)
GO

SELECT * FROM dbo.tbl_user
GO

Row Level Security (RLS) 1.5

Step 7:
Lets create a separate scheme (Security) which we will use to create RLS object (predicate function & security object) later in this article. Note : It is a best practice to create RLS object in a separate scheme. Given below is the script.

USE Sample_DB
GO
CREATE SCHEMA Security;
GO

Step 8:
Now, it is time to create a predicate function (inline table valued function) to make restricted access for each branch data & for each user. However, db_owner will have full access on the table. Given below is the script.

USE Sample_DB
GO
--DROP FUNCTION [Security].fn_securitypredicate
--GO
CREATE FUNCTION [Security].fn_securitypredicate(@BranchName varchar(50))
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
    FROM dbo.tbl_user a Where  (a.DatabasePrincipalID = DATABASE_PRINCIPAL_ID()
	And a.BranchName = @BranchName) OR IS_MEMBER('db_owner')=1
GO

Step 9:
Once predicate function (inline table valued function) has been created, lets create a security policy and add the predicate function (created in the above step) as a filter predicate. Please make sure that the STATE must be ON. Given below is the script.

USE Sample_DB
GO
--DROP SECURITY POLICY [Security].[BranchSecurityPolicy]
--GO
CREATE SECURITY POLICY [Security].[BranchSecurityPolicy]
ADD FILTER PREDICATE [Security].fn_securitypredicate([BranchName])
ON [dbo].[tbl_Sample]
WITH (STATE= ON);
GO

Step 10:
Now, we are done with the setup. Lets test the access of each user on the table (tbl_Sample). Given below is the script.

  • USER_A can view ONLY Branch A data as shown below.
USE Sample_DB
GO
EXECUTE('Select * from tbl_Sample;') AS USER ='USER_A'
GO

Row Level Security (RLS) 1.6

  • USER_B can view ONLY Branch B data as shown below.
USE Sample_DB
GO
EXECUTE('Select * from tbl_Sample;') AS USER ='USER_B'
GO

Row Level Security (RLS) 1.7

  • Admin, whose is member of db_owner can view Entire Table data as shown below.
USE Sample_DB
GO
EXECUTE('Select * from tbl_Sample;') AS USER ='Admin'
GO

Row Level Security (RLS) 1.8

Conclusion :
As you can see above by creating few RLS objects we can restrict each user access at the row level even though the user has Select access on the respective table.

Reference : MSDN

Advertisements

Read Full Post »

Policy Based Management (PBM) is one of the greatest features shipped with SQL Server 2008. It made DBA’s life easier when it comes to implementation, compliance as well as changing any database/ server properties, especially when you need to implement /check some database / server settings across the databases/ servers. Recently, we have implemented Delayed durability database setting to Forced in one of our clients’ databases using Policy Based Management across the databases. Delayed durability is actually one of the best features shipped with SQL Server 2014. What made it best actually is its handling vs performance. You can just enable with a single statement and you get a huge performance boast.

 Note: Delayed durability is disabled by default. 

In this article, I will show you step by step, how to implement any database / server settings across the databases / servers using few clicks with the help of Policy Based Management.

Step 1:
Open SQL Server Management Studio (SSMS) and Select Management >>Policy Management >>Conditions and right click on it to Select New Condition… as shown below.

PBM - Delayed Durability 1.0

Step 2:
Now, we are in condition building screen, so lets build the 1st condition and that is to check delayed durability in all user databases which is online ONLY. Given below is the configuration. Do not forget to give a proper name to this condition.

PBM - Delayed Durability 1.1

Step 3:
Lets repeat Step 1 and open the condition creation screen. This time we will create the condition to check database setting which is delayed durability=FORCED. Given below is the configuration.

PBM - Delayed Durability 1.2

Step 4:
Now we are done with the conditions, so the next step is to build the Policy. In order to build the policy you need to Select Management >>Policy Management >> Policies and right click on it to select New Policy… as shown below.

PBM - Delayed Durability 1.4

Step 5:
Now, we are in policy creation screen, here we need to give a proper Policy Name and then select the conditions created in Step 2 and Step 3 shown below in red and blue color border respectively.  In  other words, we are trying to make policy which checks the delayed durability is forced or NOT in all user databases which is online.

PBM - Delayed Durability 1.6

Usually I choose the Evaluation Mode to “On Demand” showing above because it is the easiest way to evaluate and fix any discrepancy across the databases / servers, however, you can select On Schedule as well and Press OK.

Step 6:
Now, the policy has been created and you can see it under policies as shown below. Just right click on and select Evaluate to evaluate the policy as shown below.

PBM - Delayed Durability 1.7

Step 7:
You are in evaluation screen and you can see all the user databases shows in red color because none of them is having database settings to delayed durability= force.

PBM - Delayed Durability 1.8

Step 8:
Lets click on View Details to see the difference in database settings as shown below. You can see that Policy is expecting the Delayed durability = force but the actual value is disabled.

PBM - Delayed Durability 1.9

Step 9:
Lets select the databases where you want to change the database settings of delayed durability to FORCE and press Apply button. It gives the given below messages. Press YES button.

PBM - Delayed Durability 1.10

Step 10:
Once the policy has been implemented, policy based management will automatically evaluate the policy again and shows as green color icon as shown below.

PBM - Delayed Durability 1.11

Let me know if you have implemented any databases / servers settings using Policy Based Management.

Read Full Post »

Yet again I had another opportunity to present Introduction to Policy Based Management yesterday (26 May 2015) at SQL Server User Group meet-up @ Microsoft Office, Dubai, UAE. As mentioned earlier, this event is normally held every last Tuesday of the month. Meet-up was organised by SQL Server User group (www.uaessug.com) and usually consists of two comprehensive and informative sessions that focus on the SQL Server latest technology, best practice, user experience, tips & tricks. This is a good platform to network with SQL Server experts face to face. I was the speaker for yesterday’s meet up along with Mr. Mohammed Owais (CTO at CAZAR).

Following topics were covered in yesterday’s session:

  • Backups – not as simple as you think presented by Mr. Mohammed Owais
  • Introduction to policy based management was by me

We focused on how you can make your restore strategy instead of backup strategy 🙂 and discussed different types of backup and how can it be handy in case of disaster. Also we discussed, how can you manage your policies across the servers by just click of a button.

I hope yesterday’s session was factual as usual. Our next meetup will be held in June 2015. Hope the coming sessions would witness more participants.

Some glimpses of the session:

Mr. Owais busy explaining Backup :

IMG_9815_U

He explained us why tail log backup is important :

IMG_9816_U

Further he explained, how to boast performance of backup :

IMG_9818_U

Me presenting Introduction to policy based management :

IMG_9820_U

Explaining PBM with the help of demos :

IMG_9821_U

IMG_9826_U

Last but not least, lunch 🙂

IMG_9828_U

IMG_9829_U

IMG_9831_U

Thank you guys. See you next month.

Read Full Post »

SET NOEXEC is one of my favorite SET statements. I am sure, when I explain it in detail you will love it too. Mostly, we come across in situations when we need to execute batch SQL statements (insert, update, delete) on production databases for the deployment / troubleshooting etc. Sometimes due to the syntax error, some parts of the batch statements execute and the remaining DO NOT and it creates the mess/headache for you to correct the data. And you are left with no option, but to restore the latest backup and fix the script and execute it again.

The solution for this problem is SET NOEXEC. It actually compiles the SQL Query and validate the syntax. The best/fun part is that it DOES NOT EXECUTE the SQL Query. So even if SQL Query is having errors, it DOES NOT affect the data and you are SAFE.

Let me create a sample to demonstrate, how it works.
Sample :

USE AdventureWorks2014
GO
--DROP TABLE tbl_sample
--GO
--Create a sample table
CREATE TABLE tbl_sample
(
 [ID] INT,
 [Letter] VARCHAR(1)
)
GO
--Insert a record in the table
INSERT INTO tbl_sample VALUES (1,'A')
GO
--Browse the data
SELECT * FROM tbl_sample
GO

NoEXEC.1.1

Now, let me turn on the NOEXEC and execute few statements and view the result-set.

USE AdventureWorks2014
GO
-- SET NOEXEC to ON
SET NOEXEC ON;

--Update the table
UPDATE tbl_sample SET [Letter]='B' WHERE [ID]=1
GO
--Delete the table
DELETE FROM tbl_sample  WHERE [ID]=1
GO
-- SET NOEXEC to OFF
SET NOEXEC OFF;
GO

NoEXEC.1.2

The output shows Command(s) completed successfully. It seems, that it not ONLY compiles but executes as well. Lets browse and check the impact of above queries on the data.

USE AdventureWorks2014
GO
--Browse the data to check the impact.
SELECT * FROM tbl_sample
GO

NoEXEC.1.1

As you can see above, the SQL Statements (Update & Delete) DID NOT IMPACT sample table data because NOEXEC was TURNED ON. Hence, it is PROVED that it ONLY compiles the SQL Statements and gives you either an error or Command(s) completed successfully message but it DOES NOT execute SQL Statements.

Note : Once you compiled the statement, DO NOT FORGET to turn NOEXEC off.

Read Full Post »

Yesterday (24 March 2015), I had the opportunity to present Extended Events at SQL Server User Group meet-up. This event was held at Microsoft Office in Dubai, UAE. Generally this event is held every last Tuesday of the month. SQL Server User group (www.uaessug.com) have been organizing it extremely well for the last one year. This meet up usually consists of two comprehensive and informative sessions that focus on the SQL Server latest technology, best practice, user experience, tips & tricks. I usually attend this meet up because I get an opportunity to network with SQL Server experts face to face instead of Facebook chat :). I was the speaker in yesterday’s meet up along with Mr. Mike Mohan (CFO at Microsoft).

Following topics were covered in yesterday’s session:

  • Real Life Power BI presented by Mr. Mike Mohan – Microsoft
  • Introduction to Extended Events was by me

We focused on how a non technical user can make best use of Power BI & its integration on different devices, best practices and troubleshooting (the scariest part) throughout the event and hands on with live demos to demonstrate the practical implementations of Power BI & Extended Events.

I hope yesterday’s session was informative as usual. Our next meetup will be held in April 2015. Hope the coming sessions would witness more participants.

Some glimpses of the session:

Mr. Mike busy explaining Power BI :

IMG_0079_U

He showed us how to generate a power map and its auto zoom feature :

IMG_0080_U

Showed us, how to combine many graphs in one page :
IMG_0083_U

Displaying look n feel of Power BI on multiple devices :

IMG_0092_U

Me presenting SQL Server Extended Events :

IMG_9654_U

Showing the components of EE :
IMG_9656_U

IMG_9657_U

Thank you guys. See you next month.

Read Full Post »

Remove-Duplicate-WordsI have been asked this question how to remove the duplicate words in a sentence, when my team and I were busy in messaging legacy application data and we had to migrate it to SQL Server 2012. Also, we had to check and clean the data if there was any duplicate word in a sentence. So I started scripting and checking multiple options to develop this solution including loop & XML, but I usually prefer XML. So I finally developed the solution using XML.

Before proceeding with the solution, I would like to create an example to demonstrate the solution.

--DROP TABLE tbl_Sample
--GO
CREATE TABLE tbl_Sample
(
 [ID] INT IDENTITY(1,1),
 [Sentence] VARCHAR(MAX)
)
GO
INSERT INTO tbl_Sample 
VALUES ('This is the the test test script from from raresql.com')
GO
INSERT INTO tbl_Sample 
VALUES ('This should should remove duplicates')
GO

The script of this solution is given below. and can be downloadable from here.

--DROP FUNCTION dbo.[UDF_Remove_Duplicate_Entry]
--GO
CREATE FUNCTION dbo.[UDF_Remove_Duplicate_Entry]
(
      @Duplicate_Word VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
BEGIN
	  DECLARE @Xml XML
	  DECLARE @Removed_Duplicate_Word VARCHAR(MAX)
	  SET @Xml = CAST(('<A>'+REPLACE(@Duplicate_Word,' ','</A><A>')+'</A>') AS XML)

      ;WITH CTE AS (
	  SELECT
	  ROW_NUMBER() OVER(ORDER BY A) AS [Sno],
	  A.value('.', 'varchar(max)') AS [Column]
	  FROM @Xml.nodes('A') AS FN(A) )

	  SELECT @Removed_Duplicate_Word =(SELECT Stuff((SELECT '' + ' ' + '' + A.[Column] FROM CTE A
	  LEFT JOIN CTE B ON A.[Sno]+1=B.[Sno]
	  WHERE (A.[Column]<>B.[Column] Or B.[Sno] is NULL)
	  FOR XML PATH('') ),1,1,''))

	  RETURN @Removed_Duplicate_Word
END
GO

SELECT
[ID]
,[Sentence] As [Before Duplicate removal]
,dbo.[UDF_Remove_Duplicate_Entry]([Sentence]) As [After Duplicate removal]
FROM tbl_Sample
GO
--OUTPUT

How to remove the duplicate words in the sentence.1.1

Let me know if you come across this scenario and its solution.

Read Full Post »

I came across this scenario many times when you need to find the largest tables in the database, specially at the time of maintenance or support. Fortunately we do have a solution in the shape of different scripts that you can easily find in many blogs and sites. In fact, I have written a solution earlier. In this article, I will discuss how to find the largest object without writing a single line of script.

Let me explain it step by step by step.

Step 1 :
First, open SQL Server Management Studio (SSMS) and select the target database (where you need to find the largest object) and right click on it as shown below.

Find largest table 01

Step 2 :
Once you right click on the target database you will see a lot of options. Select reports \ standard reports \ Disk Usage by Top Tables as shown below. There are other very useful reports that give you the statistical data about your SQL Server.

Find largest table 02

Step 3 :
Once you click on reports \ standard reports \ Disk Usage by Top Tables, SSMS will open a report showing which tables are using more Disk spaces, reserved space & having highest record counts etc as shown below.

Find largest table 03

Step 4 :
Now you have the report and by default it is sorted by Reserved (KB), but you can sort it with any column (# Records (Actually record count), Reserved(KB), Data(KB), Indexes(KB), Unused(KB)) by clicking on up and down sign as shown below.

Find largest table 04

The beauty of this feature (Standard reports) is that you can generate the statistical report within few seconds, just by click of a menu. In addition you can export / print this report in a nice format without any extra effort as shown below.

Note : Please do not forget to refresh this report before export or print ;).

Let me know if you tried this solution and how did you like it ?

Read Full Post »

« Newer Posts - Older Posts »