Voice of the DBA

Author: way0utwest

  • Checking Permissions

    Someone posted this query recently:

    select a.*,name, b.* from sys.database_principals a, sys.database_permissions b

where permission_name = 'INSERT' and b.grantee_principal_id = a.principal_id

    That’s a little ugly, so let’s fix it:

    SELECT  a.name, a.principal_id, a.is_fixed_role
      , a.default_schema_name
      , b.permission_name, b.permission_name
      , b.state_desc
 FROM sys.database_principals a
  INNER JOIN sys.database_permissions b
  ON b.grantee_principal_id = a.principal_id
WHERE permission_name = 'INSERT'

    If you run this, you’ll get INSERT permissions in your database. In this case, the person had one row returned that had “public” in it, as shown.

    results1

    I normally don’t have permissions for public, but in this case I had run this first:

    GRANT INSERT ON Person.Address TO Public

    I don’t recommend permissions for public, and you really ought to run this on all your servers:

    SELECT a.name, a.principal_id, a.is_fixed_role
      , a.default_schema_name
      , b.permission_name, b.permission_name
      , b.state_desc
 FROM sys.database_principals a
  INNER JOIN sys.database_permissions b
  ON b.grantee_principal_id = a.principal_id
WHERE a.name = 'public' AND major_id > 0

    How do you find out which objects have permissions? There’s a clue in the last query. If you scroll across in the results, there’s a major_id column. You can use that to find the object.

    results2

    The OBJECT_NAME function is handy here, and it takes an object_id, which is the major_id. If I run this:

    SELECT OBJECT_NAME(85575343)

    I get “Address” back, which is the object I altered.

    And, of course, we need to clean up

    REVOKE INSERT ON Person.Address TO Public

  • Working Long or Working Hard

    Do you want to work hard or long?

    Seth Godin has some very interesting things to say and often makes me think with his short posts on the world. I saw him speak a few years ago, enjoyed it, and while I don’t always agree with the way he views the world, his comments and thoughts often cause me to rethink my position. Recently he had a blog on Hard Work vs. Long Workthat made me stop and think about my career.

    Seth talks about the difference between those two types of effort, with most people dreading hard work more than long work. I think most people would say that they’d rather work hard for 8 hours a day then work for12 at a lesser pace. Most people in IT seem to not want to work long hours, especially as they age, and would prefer to work hard for a shorter period of time. However,  my guess is most workers tend to shy away from hard work more than long hours.

    Why is that? Seth nails the reason when he mentions risk. Doing hard work involves risk, it involves taking chances and trying to fundamentally do something that is hard. If it wasn’t a challenge, we wouldn’t call it “hard” work, and everyone would tackle it. However the risk introduced by taking on a difficult task is daunting to most people, especially if failure might involve consequences. Most people avoid increasing their risk, even at the expense of spending more time coasting along.

    We see this every day in our jobs, and I know I’m not immune. I implement change slowly, and tend to shy away from risk. I do take some changes, but they are calculated efforts, without too much downside.  Both at work and in the rest of my life, I am definitely risk-averse.

    Should you be doing hard work instead of long work? I can’t answer that for you, but I would urge you to think about the differences, and understand what you are more comfortable doing. It pays to leave your comfort zone as you often grow, but being uncomfortable with the way you work is also a large source of stress in your life. And stress is best dealt with in short doses, not long periods of time.

    Steve Jones

    The Voice of the DBA Podcasts

  • Colorado SQL Server User Groups – May

    I saw a post from Marc Beacom on the presentations this week for various groups. You can get the info on them all from Marc’s post, but basically this is user group week in CO.

    • Tuesday – Boulder
    • Wednesday – Colorado Springs
    • Thursday – Denver

    I’m not sure I’ll be at Denver on Thursday. I’d like to go, but I’m having a busy week. I definitely can’t make the other two with my wife out of town this week.

    In terms of the presentations, I haven’t seen Mike Fal (Boulder) talk before, but permissions can be a little hairy and this could be a good session. I do like Tom Norman, and he works in the payment/credit card industry, so I am sure that his Encryption talk on Wed will be good. Thur I’d like to see Jason Horner do the short spatial piece since it’s an interesting topic and I’ve been bothering Jason to talk for a few years. Glenn Berry’s Hardware talk is a good one, especially for people that don’t track that stuff. It’s all just “stuff to me” and Glenn is the person I tend to ask for advice if I’m looking for new hardware. Worth going to see.

    Enjoy the SQL week in CO, and if you would like to come speak, give a ring. You can do a one week tour, hitting all three group sometime and we’ll line up some hosts to ensure you have fun. If you want to come ski from Nov-Apr, I’ve love to have you.

  • Competing with an MBA

    It seems that if you want to move up in management, you need an MBA. At least that was the prevailing wisdom a few years back as I worked in various corporations. Most executives were expected to get an MBA, and most did. There was an explosion of “executive MBA” programs in the late 90s and early part of this decade allowing employees to earn an MBA while still working in a full time position.

    However a lot of the MBA program was geared towards teaching hard topics, like finance and accounting, and ensuring that graduates understood the roles of information technology in a company. I think that was a bit of a mistake as we ended up with a generation or two of managers who viewed ROI, profit margin, and the bottom line as the most important parts of a company.

    Companies are realizing that becoming an executive and leader takes more than understanding numbers, and are starting to look to hire candidates that have stronger “soft skills” that emphasize working with others, understanding other viewpoints, motivating employees, and relating to customers. Business schools are starting to catch up in these areas and looking to offer more courses that deal with softer skills.

    If you are interesting in advancing into management, an MBA might be a good investment, but I would also argue that building strong soft skills is just as important. If you demonstrate strong abilities to interact with others, solve problems, handle disagreements, and act as a leader, you might be just as strong a candidate as that recent MBA graduate.

    There’s a lot more to IT than technology, and a lot more to business than crunching numbers. Those soft skills will be valuable in every facet of your life, and are worth spending some time on in your personal development plan.

    Steve Jones

    The Voice of the DBA Podcasts