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