It’s T-SQL Tuesday again, and I’m not prepared (again). I missed all the tweets last week while I was at the MVP Summit, so now I need to try and find time today to knock out a post.
It’s work, and effort, but I enjoy participating in this party, so I’ll be digging trough my mental inventory of stuff for this month later today.
One of the changes coming in SQL Server v11 (not sure what the name will actually be), also known as “Denali”, is the addition of more High Availability/Disaster Recovery Options. When I first heard about this, I was excited, but the public CTP had limited support for it. I’m hoping the next CTP will substantially improve the feature and this makes it to the RTM.
In the meantime, here’s a Channel 9 video from Justin Erickson that gives a short explanation of how this works and how it compares with the current Mirroring/Log Shipping/Clustering options.
I saw a note recently where someone asked what permissions were needed for a user to execute TRUNCATE TABLE. In previous versions we needed ownership of the table or DBO level permissions. I had thought this was changed in SQL 2005 to require just the CONTROL permission.
However when I checked the TRUNCATE Books Online page, I found this: The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.
Alter permissions is the minimum?!?!!?
That sounded fishy, so I did this. First I created a new user, with no permissions other than public. My user was, appropriately, MyTestUser.
Next I created a table and granted permissions:
CREATE TABLE TRLC ( est_no varchar(10) default ' ' , right_no int default 0 ) GO INSERT TRLC SELECT 'Test', 1 GRANT CONTROL ON TRLC TO MyTestUser
I then opened up another Query Window and changed the connection to use MyTestUser. This user only had CONTROL permissions and nothing else. A quick test showed that this user could indeed clear out the table. This:
TRUNCATE TABLE dbo.TRLC
executed without error.
I think Books Online needs an update, and I’ll submit a note to that team to clarify this.
This presentation is designed to cover some of the basic mistakes that I find people making quite often when working with SQL Server. It is a mix of development and administrative items, designed to help beginners get a grounding in those skills that often cause the most problems in SQL Server.
The talk is 75 minutes.
Slide Decks:
Voice of the DBA 