Column level permission

Uh,it's so hard to back to work from the vacation:-).Yesterday,for the first time, I have been faced the requirement to implement a column level permission on the table, have you done something before? A little script below demonstrates it.

USE dbname;
CREATE TABLE demo (c INT, c2 INT)

GRANT SELECT (c) ON demo TO test


EXECUTE AS USER = 'test';

SELECT * FROM fn_my_permissions('dbo.demo', 'OBJECT')
ORDER BY subentity_name, permission_name ;

REVERT

PS: subentity_name tells you on what column given permission.

It's The Time To Go on Vacation

Yep, on 21st May I am going to Moscow to see a World Table Tennis Championship. I love playing table tennis and even took a part of two World Table Tennis Championships in earliest 90. So I hope to meet there may old friends from all around the world, so I am very exited about it.. See you later on.

Kalen Delaney's seminar

Just backed home from the three days of seminar in Tel-Aviv named "Deep dive into SQL Server Internals with Kalen Delaney".
Two days Kalen explained about SQL Server Query Plans,Tuning and Recompile techniques and the last one was about SQL Server Data Storage Formats.

You know , I thought I know a lot about SQL Server performnace tunning and etc, however, attending that seminar I just realized how much I need to learn. There are lot if tips,thoughts how to tune the system, indexes and etc.

I would also mention about how easy was to understand Kalen (she spoke english at seminar :-)) and her methods to provide courses which is so important for attendants.

Don't speak to the DBA while he/she is doing the job.

Our DBA was very busy today, helped out our developers top write efficient code,explained to the programmer how to launch the old DTS package on SQL Server 2005 and etc. You know how it is, someone comes in and asking the question and you answer the question at the same time you are doing the work.
So,today answering one such questions our DBA deleted by mistake very ctitical database. Fortunately , we had zero data loss thanks to our backup/recovery strategy.

I think that is acceptable to say that you are busy right now please ask the question later on, what do you think?

Getting column level permision report

If you think about giving column level permission , you'll probably create a view with only needed columns to expose to ,then grant SELECT permission on that view and absolutely DENY access to underlaying tables to the user, right? Also , it depends on the policy of the company but you are able to grant SELECT/UPDATE/DELETE/INSERT operations on column level, have you ever used it:-)? So I recently visited our client who does perfom security permission on column level and wanted to know what are the columns and on what tables that specific user has access? I decided to perform the below testing, see if that might help you.

CREATE TABLE demo (c INT, c2 INT)

/* Grant SELECT permission on column named c on demo table*/
GRANT SELECT (c) ON demo TO test


EXECUTE AS USER = 'test';

SELECT * FROM fn_my_permissions('dbo.demo', 'OBJECT')
ORDER BY subentity_name, permission_name ;

REVERT

Have you dealt with ENTER key in INSERT table?

I have a client who uses the application that allows to insert data but also typing ENTER key in the middle. Well, the question is how do you want to store the data and later displaying on the client? He was not sure , so I showed him the below technique to display such kind of data....


--Create auxiliary table
SELECT TOP 8000 n = IDENTITY(INT)
INTO Numbers
FROM syscolumns s1, syscolumns s2 ;

CREATE TABLE test (col1 varchar(8000))

INSERT INTO test values ( 'Row----------1
Row--------2
Row--3
Row----4'
)

---See how it stores the data.
SELECT * FROM TEST

--Remove ENTER key
SELECT SUBSTRING(col1, n, CHARINDEX(CHAR(13), col1 + CHAR(13), n) - n)
FROM Numbers, test
WHERE SUBSTRING(CHAR(13) + col1, n, 1) = CHAR(13)
AND n < LEN(col1) + 1 ;

--Cleanup
DROP TABLE Numbers
DROP TABLE test

I was invited to blog on www.sqlblog.com

A few days ago Adam Machanic who is the owner of that great web site invited me to join his great company. I am really excited about and proud to be part of such great company of friends who write very good stuff about SQL Server.
I would like to keep bloging here as well , I do not know how long it will take but you can always catch me up on http://sqlblog.com/blogs/uri_dimant/