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/

Think before unchecking sysadmin rights of BUILTIN\Administrators.

I have recently met our client who uchecked the sysadmin rights of BUILTIN\Administrators group before given any permissions to anotgher account.

That was NOT such problem if the BUILTIN\Administrators group was removed from sysadmin role accidentally/by mistake, then you must login with another sysadmin login. If there is no other sysadmin login, you must login with SQL authentication as sa with the password that was set during setup to sa. Once logged in as a member of sysadmin, you are able to add BUILTIN\Admisnitrators back to sysadmin role.
However everything above does not work for the client. Uhhh,the client also disabled SA accoount as well as DAC connection.
Moreover, there is no domain controller where you can create a sysadmin domain acoount and grant the access to the machine running SQL Server,that was a stand alone computer with single instance installed on.

The solution we found was to start SQL Server with single user mode. Using the single-user mode, SQL Server 2005 prevents a Windows Administrator to abuse this privilege to act on behalf of the sysadmin without being noticed. This allows Windows Administrator accounts to perform certain maintenance tasks, such as installing patches. To someone who is not familiar how to start the instance in single user mode and adding login to the server role being system administrator please read the below link describing step by step the procedure.

http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

SQL_VARIANT_PROPERTY() to find the info about data type?

BOL says that this function returns the base data type and other information about a sql_variant value. Have you ever looked what is datatype of GETDATE() or perhaps DB_ID() function or what is the data type of SYSTEM_USER??

DECLARE @var sql_variant
SET @var =GETDATE() --SYSTEM_USER
SELECT SQL_VARIANT_PROPERTY(@var , 'BaseType') as BaseType
, SQL_VARIANT_PROPERTY(@var , 'Precision') as Precision
, SQL_VARIANT_PROPERTY(@var , 'Scale') as Scale
, SQL_VARIANT_PROPERTY(@var , 'TotalBytes') as TotalBytes
, SQL_VARIANT_PROPERTY(@var , 'MaxLength') as MaxLength
, SQL_VARIANT_PROPERTY(@var , 'Collation') as Collation;

I found that very usefull as you can easily retun the basic info about even system objects and how SQL Server interpetes it.

Think before you use ALTER TABLE ....

Let me say you have been told to add a computed column to the very huge table in SQL Server. Well,if you have been working with SQL Server for a while you are probably aware that you will be better of using ALTER TABLE.. command instead of using SSMS because under some circumstances SQL Server will copy the entire table into a temporary one and then rename it , but if the users work on the table they will be locked... So far so good. Now , what if you have been told to add a computed column with PERSISTED clause which means SQL Server will store physically the values of the column..Here stop and think a little bit especialy you deal with huge tables. SQL Server is going to update every row in the table with computed value based on the formula you provide with. How long will it take? I have seen that adding a PERSISTED column by using ALTER TABLE command on the table with 230 million rows took 19 hours on very powerful server, can you afford it?.
So what is the solution? The solution is to add permanent column and then running an UPDATE command , however do not forget to divide the UPDATE into small batches (each one of 10000 rows for example) such as you will control the rows has been updated/affected by the UPDATE and your LOG file will not be blowing up.

Consistency checking for a 1.2 TB database

I would like to share with you my recent experience on how to build consistency checking for VLDB by using DBCC CHECKDB/CHECKTABLE and etc. I was following Paul's great article
http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-Consistency-Checking-Options-for-a-VLDB.aspx
to build the same or almost the same strategy on the client's site. They have 8 huge tables arround 658,245,225 pages each table and I tested the bigest one to run DBCC CHECKTABLE command. It took 17 hours to complete the task but unfortunately it was not acceptable for the client. So my final solution is (thanks to Paul) to restore the database on the dev.machine and perform the checking on.