Voice of the DBA

Tag: T-SQL

  • ALTER SCHEMA TO ADD PERMISSIONS

    I’m sure some of you have wanted to do this:

    ALTER SCHEMA Steve AUTHORIZATION Steve

    You realize this doesn’t work, and you can’t grant the user Steve, rights to his schema after it’s created. You can do this:

    CREATE SCHEMA Steve Authorization Steve

    UPDATE: Someone pointed out this works after the fact:

    ALTER AUTHORIZATION ON SCHEMA::Steve TO Steve

    But not alter it. Strange and annoying. In my last post, I showed dropping and recreating the schema. That works well if you are beginning development, but not when you’re in the middle.

    Let’s make this less confusing and see how we actually allow a developer to access a schema to create procedures (or other objects) when the schema exists.

    First, let’s assume we want a developer, Steve, to be able to create procedures in the ETL schema. We have these conditions:

    • The ETL schema exists
    • The ETL schema is owned by another developer.
    • The login and user, Steve, exists in this database with no permissions.

    I want to now allow Steve to build the procedure ETL.MyProc.

    Grant Permissions

    The first thing I do is grant create procedure permissions to Steve.

    CREATE LOGIN steve WITH PASSWORD = ‘Test’;
    GO
    USE Sandbox
    GO
    CREATE USER Steve FOR LOGIN Steve
    GO
    GRANT CREATE PROCEDURE to Steve;

    GO

    With this done, now let’s set up our schema.

    CREATE SCHEMA ETL
    GO
    There are no default permissions, so the user Steve cannot create ETL.MyProc right now. How do we fix this?

    The trick here is that I need to allow Steve to ALTER the schema. I can do this by using this statement.

    GRANT ALTER ON SCHEMA::ETL TO Steve;
    GO

    I could do other things. I could grant CONTROL. to Steve instead, but I might not want to do that. That gives Steve the ability to actually drop the schema, which probably isn’t want. It’s certainly not the “least permissions” to let the developer create objects in a schema.

  • The Worst Comments

    I was watching a presentation recently on refactoring C# code and was amazed by some of the comments that the speakers showed in the code. The example was a real application that had been obfuscated and simplified a bit for the talk. The comments, however, had only been changed when they might disclose a specific person or company. The speakers pointed out a few of those changes, but also noted that most of the comments were verbatim from the original code.

    Comments like “Dave changed this from the old way”  or “Bug 445: as per the operations group” were good examples of bad comments. These items don’t really help a developer understand the code. The comments in application code should be there to add to the code itself, helping someone understand a reason for the code, not an obscure reference or an obvious statement (“this code adds two balances together).

    With that in mind, I’m sure many of you have come across some comments in code that have evoked a wide range of emotions. I’m sure you’ve been frustrated, annoyed, or something else. Perhaps even from your own comments. With that in mind…

    What are the worst comments you have found in code?

    I hope you don’t have examples in your current application, but perhaps you do. Perhaps you have even committed your own code recently without really taking the time to accurately describe the change. Maybe you want to go look in your VCS and see what you’ve entered lately.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.0MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Altering a Column with NOT NULL

    A short piece, as I ran into the need recently to alter a column to NOT NULL status. I’ve rarely done this in the past, usually specifying NOT NULL when I create the table. Often in future changes, I’ve been wary of not allowing NULLs since I’ll always find an application, or worse, a business situation where there is no good value available. However that’s a separate discussion.

    Altering the Column

    Let’s say I have a column that is specified as NULL in a table, and I want to change that. I initially tried this:

    ALTER TABLE Tags ALTER COLUMN Status NOT NULL;

    However, I got a syntax error. For the life of me, I couldn’t understand why, so I looked up the syntax. If you look at the ALTER TABLE syntax, it shows that the ALTER COLUMN item needs the type included. While I am not changing the data type, to alter the column, I need to do:

    ALTER TABLE Tags ALTER COLUMN Status tinyint NOT NULL;

    Another inconsistency in SQL. We don’t provide the whole definition again, and here we need to provide the column definition, even when only changing one of the settings.

  • Debugging SQL Server

    One of the tools that I found useful early in my development career was the debugger. Being able to track the values of variables, check the call stack, and pause execution of programs was handy. Early in my career, the tools were very rudimentary, but the latest debuggers in Visual Studio are quite advanced. I remember using a great debugger in Rapid/SQL years ago that helped me with some SQL Server 2000 code.

    There are debugging tools included with SQL Server, but the last time I used them, they seemed to be a bit flaky. However the need to follow your code slowly along it’s execution plan hasn’t changed. I’m curious this week, what many of you do inside of SQL Server to debug your code. I wanted to ask you this week:

    How do you debug your applications that work with SQL Server?

    These could be .NET applications that query the database. You could have ETL processes using SSIS or some other tool that you work on. Perhaps you have a system that runs entirely inside SQL Server and you need to untangle your T-SQL.

    Do you use Visual Studio tools? Have you configured the T-SQL debugger? Are you a PRINT statement or temp-table-for-results developer? Perhaps you have logging or some other mechanism that you use?

    Let us know this week what works well for you, and if you’ve found a particular technique to be handy in a situation, we’d love an article that might teach someone else how to debug their code.

    Steve Jones

     

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 1.7MB) podcast or subscribe to the feed at iTunes and LibSyn.