SQL-Saturday #116 – Question of the day: When does a GRANT overrides a DENY in SQL Server?

January 7, 2012 5 comments

SQL_SAT

 

SQLSaturday #116 – Bangalore

WoW!! What a great day it was to learn and connect with SQL guru’s and like minded folks who carry same passion for SQL server as I do. It’s quite an honor to meet and listen @blakhani, @pinaldave, @banerjeeamit, @vinodk_sql, @kashyapa , Rick @Joes2Pros……You guys are truly inspirational

Fellow attendees -  For those of you on Twitter, follow #sqlpass and make sure to check out the #sqlsat116 and #sqlsaturday hashtags to stay up to date

As tweeted by @vinodk_sqlRace to First 10 blog post on #SQLSat116. If you attended the event, we want to hear from you. Drop us a nudge :) …”

Here’s the first one to start with –

Fellow attendees – During the session on Security Pitfalls, Vinod gave us this interesting question to reply/tweet later.

Question: When GRANT overrides DENY in #sql server? Was asked during a session today in #sqlsaturday #sqlsat116

Answer: An explicit GRANT on a TABLE::COLUMN takes precedence on DENY TABLE #sqlsat116 #sqlsaturday

And here’s a simple test I did to get me answer

We needs 3 users/login (one being S.A.) 

–Here:

–UserOne is SA on SQL Instance

–UserTwo has got READ/WRITE privileges on TEST database

–UserThree is DBO (database_owner) for TESTDB

–Step1. Create a sample table say ‘iden_test’ with 2 cols (id,name)

USE [test]

GO

CREATE TABLE [dbo].[iden_test](

     [id] [int] IDENTITY(1,1) NOT NULL,

     [name] [varchar](10) NULL

)

–Step2. Run below T-SQL to DENY READ (SELECT) to UserTwo

DENY SELECT on OBJECT::dbo.iden_test TO UserTwo

GO

–Step 3. Try running below query and this should fail with permissions issues

SELECT id FROM dbo.iden_test

Go

Error: Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object ‘iden_test’, database ‘test’, schema ‘dbo’.

–Step 4. Login to server with UserThree (SA user) and run below T-SQL to GRANT

GRANT SELECT ON OBJECT::dbo.iden_test(id) TO UserTwo

Go

 –Step 5. Login again as UserTwo and run the SELECT (per Step 3) again

SELECT id FROM dbo.iden_test

Go

I can get the results for the specific column now.

Conclusion – A Grant on Table (Column) overrides the DENY on same object.

Varun as iVarund

Identify queries that consume a large amount of log space in SQL Server

July 12, 2011 2 comments

One of regular issues DBA’s get are about the T-log growth. Situations, wherein one “bad”  or “poorly-designed” query can eat up entire T-log space, bring the free space to zero and then bring your application down. The cause and remedy of most these issue is discussed in this KB # 317375(I’m  big fan on Microsoft KB’s).

While the KB discussed about the causes and approaches to deal with high T-log growth situations, it also hints about how we can ‘proactively’ find the queries that are consuming your T-log space at any given moment using DMV’s. Taking cue from this, I have written a below T-SQL Code:

Identify queries consuming large T-log space:

——————————————————————————————-
– Description: T-SQL to find queries that consume a large amount of log space in SQL Server
– Source: KB # 317375
– Author: varun.dhawan
——————————————————————————————-
SELECT dtst.session_id                                                       AS
       ‘SPID’,
       CAST(Db_name(dtdt.database_id) AS VARCHAR(20))                        AS
       ‘database’,
       der.command,
       Substring(st.TEXT, ( der.statement_start_offset / 2 ) + 1,
       ( (
       CASE der.statement_end_offset
       WHEN -1 THEN Datalength(st.TEXT)
       ELSE der.statement_end_offset
                                                                      END -
       der.statement_start_offset ) / 2 ) +
       1)                                                                    AS
       statement_text,
       Coalesce(Quotename(Db_name(st.dbid)) + N’.’ + Quotename(
       Object_schema_name(st.objectid,
                st.dbid)) +
                N’.’ + Quotename(Object_name(st.objectid, st.dbid)), ”)     AS
       command_text,
       der.wait_type,
       der.wait_time,
       dtdt.database_transaction_log_bytes_used / 1024.0 / 1024.0            AS
       ‘MB used’,
       dtdt.database_transaction_log_bytes_used_system / 1024.0 / 1024.0     AS
       ‘MB used system’,
       dtdt.database_transaction_log_bytes_reserved / 1024.0 / 1024.0        AS
       ‘MB reserved’,
       dtdt.database_transaction_log_bytes_reserved_system / 1024.0 / 1024.0 AS
       ‘MB reserved system’,
       dtdt.database_transaction_log_record_count                            AS
       ‘Rec count’
FROM   sys.dm_tran_database_transactions dtdt
       JOIN sys.dm_tran_session_transactions dtst
         ON dtdt.transaction_id = dtst.transaction_id
       JOIN sys.dm_exec_requests der
            CROSS APPLY sys.Dm_exec_sql_text(der.sql_handle) AS st
         ON dtst.session_id = der.session_id
ORDER  BY 8 DESC 

Hope that this will help you too!
 

Disclaimer: Everything here, is my personal opinion and is not read or approved by my employer before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.

What is a columnstore index?

June 16, 2011 3 comments

WARNING: The blog-post is based on pre-release software so things could change. For more details on CTP, please refer SQL Server Code-Named “Denali” CTP1 Release Notes

Upcoming SQL Product, introduces a new data warehouse query acceleration feature based on a new type of index called columnstore. Before we move any further exploring this new feature, I want to take time to explain the basics behind a columnstore index and how different is it from a traditional index (rowstore).

What is columnstore? And what is a rowstore?

To understand this, lets see a simple illustration below. Here I have a table with 4 columns (First name, Email, Phone, Street Address) . Below is a representation of how the index data will be stored and their associated pros and cons.

image

As opposed to a rowstore, a columnstore index stores each column in a separate set of disk pages, rather than storing multiple rows per page as data traditionally has been stored. So in above example, columns (First name, Email, Phone, Street Address) are stored in different groups of pages in a columnstore index.

So what’s BAD with rowstore design?

Say if we have to run a query like ‘select first_name, phone from emp’. In a rowstore design, DBMS will transfer the ENTIRE ROW from disk to memory buffer even though the query required just 2 attributes. In case of a large read intensive queries, we do so much of un-necessary disk I/O and thus wasting precious disk bandwidth.

And what’s good with columnstore design?

1. Better performance for SELECT’s – only the attributes needed to solve a query are fetched from disk, thereby saving on disk I/O.
2. Better compression ratio – it’s easier to compress the data due to the redundancy of data within a column

Really are they so good?

Wait, “There’s no free lunch”. Due to change in the index storage design, any tuple (row) writes are very expensive on a column store index. As such, in Denali, tables with columnstore indexes can’t be updated directly using INSERT, UPDATE, DELETE, and MERGE statements, or bulk load operations. Hence to perform a DML on table, we may need to disable/drop an index temporarily and then re-create post DML activity.

Hope this provides you with some initial understanding of a ROWSTORE vs COLUMNSTORE.  This feature is expected to be available in next CTP build of Denali, so once we have the build I will be able to share a demo.

Thanks of reading!

Disclaimer: Everything here, is my personal opinion and is not read or approved by my employer before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.

Hello world….once again

May 17, 2011 4 comments

Hello World !

This is continutation to my blog @ MSDN. With this blog post, I intend to help online community using SQL Server as a Database Platform, with occasional other stuff thrown in related to other RDBMS Platforms and their respective integration issues. Thanks to my wife who’d been pestering me to blog for a year or so.

I’ve spent the past few years working on various different RDBMS platforms including SQL Server, Oracle, MySQL. My current focus area is SQL Server and it’s integration with other Microsoft Technologies like MOSS, SCOM, MOM etc.

Thanks for your time reading and I hope all my effort will help (or at least entertain) you at many levels. I would really appreciate if you could let me know what you think about it, good or bad. I always appreciate feedback :)

Sincere Thanks!

Varun

Categories: Uncategorized
Follow

Get every new post delivered to your Inbox.