Voice of the DBA

Author: way0utwest

  • SQL Server Truncate Table Permissions

    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.

  • Common SQL Server Mistakes

    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:

  • The Wisdom Department

    If only wisdom were this easy to come by

    The more that I work in business, the more that I think that there truly is more to a company just maximizing the amount of money you can make. We often say that a business exists to maximize profit, but we never seem to care about the profit we can create outside of the money on the bottom line. There are all sorts of goodwill, long term growth, market share and more that often aren’t considered when running the business.

    This blog post tries to talk about how the business ought to get a makeover and establish new habits each year, just as many of us try to do with New Year’s Resolutions when the calendar changes. It’s a great goal, and while I’m not sure how many companies would try to evolve beyond the next quarter’s bottom line, I do think this is worth passing along to your management.

    In our departments, we often talk about maximizing our value to the company. Getting an amount of work done that helps the business and justifies the costs that we incur in technology and staffing. I would like to think that most of us in technology do look forward, consider the applications we build and the systems we develop as long term investments, and we build them with the idea that they will provide a long term value, not just for the next quarter.

    It might be worth setting up your own wisdom committee inside of your IT department, with the goal of ensuring long term value from the department.

    Steve Jones

    The Voice of the DBA Podcasts

  • Back from the MVP Summit

    Back home after three days in Seattle at the 2011 MVP Summit. As I wrote in an editorial this week, I can’t really talk about much, but the SQL Server development team at Microsoft is doing good work and I’m looking forward to the next few versions of  SQL Server.

     

    Relaxing at the MVP Summit

    The Summit was mostly in the Commons building at Microsoft this year. I’ve been to the building complex before, but never spent more than a few hours there. It’s a nice central location with a  variety of restaurants and shopping options for the employees. It’s a beautiful looking building, as you can see here.

     

    The Commons

    Inside it’s almost like a high end food court, where you can get all kinds of food (you have to pay), including a nice coffee shop where I managed to get coffee each morning. There were plenty of places to sit and chat, or relax, and even an inpromptu band that I saw one day.

    A good time, and valuable to me since I had the chance to meet and catch up with a number of people. As I mentioned in the editorial, we do get good dialogs with Microsoft and I think that MVPs do a great job advocating for their clients and the rest of the community.