Voice of the DBA

Tag: sql server

  • 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 Demo Setup–Attaching Databases with Powershell

    I found another use for Powershell, one actually suggested by someone else: attaching specific SQL Server databases.

    TL;DR I have a script that detaches all user databases from a SQL Server instance and then reattches certain ones. Full script at the end.

    The Issue

    We have a lot of demo databases on our demo VMs for Red Gate. Some specific databases are used to show things with different products, but it ends up with us having a few dozen databases on an instance of SQL Server.

    That’s not the best way to show things to users, as they can get confused with so many databases. Specifically for us, we have a set of databases for one of our classes, a different set for a second class, and a third set for a third class. We do this because things need to be set in different stages for each class.

    One of our sales engineers said it would be great if we could hide some databases when we didn’t need them. I immediately saw a use for Powershell here.

    Approach

    My approach to this problem would be this.

    • detach all user databases
    • attach specific databases by specifying the name of the database, and the mdf/ldf/ndf file names.
    • use a batch file the user can double click on the desktop to run the Powershell script.

    This seemed to make sense, and I started to tackle this on one of my machines in this manner. However because I detached all my databases first, all of a sudden working on things was a pain. As a result, I setup a new VM and created dummy databases there. I first worked on the attach piece, and then the detach part.

    Detaching User Databases

    This was fairly simple, and I’ve written about it before. In this case, I merely cut and pasted this code into my script.

    $srv = New-Object ‘Microsoft.SqlServer.Management.SMO.Server’ $instance

    #detach all user databases
    $dbnames = $srv.Databases.name

      foreach ($dbn in $dbnames) {
        Write-Host $dbn
        if ($dbn -ne "master" -and $dbn -ne "model" -and $dbn -ne "msdb" -and $dbn -ne "tempdb") {
          $srv.DetachDatabase($dbn, $false)
       
          }
        }

    The first line is actually needed for both parts of the script, and we re-use that object later.

    The script gets a handle to the databases object and then a collection of all the names. We loop through the collection and if we aren’t looking at one of the four system databases, we call the detachDatabase method.

    Note that this means I’m in control of the instance and I know I don’t have a distribution database or anything else that might break. For me, I can safely drop everything other than master/model/msdb/tempdb.

    Attaching Databases

    I had to search around for some example code. I guess I didn’t have to, but the docs from MS can be tricky to put together, so I searched and found a few examples. Specifically, I ran across this post that described how to attach a single database.

    I decided to begin by building up the db name and paths to the files. I started by setting a variable to the path and database name.

    $sqldatapath = "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\"

    $dbn = "sandbox"

    One of my databases is “Sandbox” and the path for all my database files is given as the default.

    Next I build up the mdf/ldf files. In my case, I don’t have anything other than single mdf file databases.

    $mdffiles = $sqldatapath + $dbn + ".mdf"
    write-host $mdffiles
    $ldffiles = $sqldatapath + $dbn + "_Log.ldf"
    write-host $ldffiles

    With these, I now can tell what I’m doing. I write the data out to the host, mostly so that if something breaks, the user can determine where. We’re all technical, but it’s nice to know what’s broken.

    These are the important bits, but now I need a place to store them. At only one time in the script, I create a new StringCollection object.

    $dbfiles = New-Object System.Collections.Specialized.StringCollection

    I’ll reuse this object for each database. In this object, I store the database file names. I use the .Add method to get them in here.

    $dbfiles.Add($mdffiles)
    $dbfiles.Add($ldffiles)

    Now I have all my parameters. I can call the AttachDatabase method.

    $srv.AttachDatabase($dbn, $dbfiles, "sa", "None")

    The documentation says I need an owner, and for simplicity, I use “sa”. I also can specify options, but I don’t care in this case.

    This attaches my first database. However, I need to repeat this. I could build some loop and use some array, which is probably better, but for the sake of simplicity here, and preventing issues, I copy and paste this code multiple times. In my case, I have no more than 4 databases, for any environment, so I merely copy/paste this code and change the database name.

    However, I don’t want to keep adding to my StringCollection each time. In between each set of databases I need to call, I add this:

    $dbfiles.Clear()

    Now I have a few simple scripts I can modify easily, and others can understand them.

    The Batch File

    The other thing I learned with the batch file is that it doesn’t have the same context as my editing session. I had to add a line to load the SQLPS stuff at the beginning for it to work.

    Import-Module "sqlps" -DisableNameChecking

    I also had to ensure the execution policy is set on each machine, but we tend to do that when we set up the machines.

    Simplicity

    This is the simple way. It’s really not the best way, and if these scripts change much, this is a problematic way of doing things. I really should have a loop with a list of databases in one place in the script. That way if I add or remove a database, I can easily do it.

    That’s an improvement I’ll make.

    Let me also say that I have a pattern of database names, and files. If I needed to handle different file locations and varying numbers of files, I think this approach actually works better. Each section of the script can be edited easily, and separately, without worrying about complex logic.

    I like simple.

    Scripts

    The batch script is this.

    powershell c:\Utilities\attach_demodbs.ps1

    I call the Powershell host and give a fully qualified path to the script.

    Here is one of my demo scripts, for two databases: sandbox and EncryptionPrimer:

    <#

    Attach Demo Databases

    This script detaches all user databases and then attaches the following databases

    Attaches
    – Sandbox
    – EncryptionPrimer

    #>

    Import-Module "sqlps" -DisableNameChecking

    $srv = New-Object ‘Microsoft.SqlServer.Management.SMO.Server’ $instance

    #detach all user databases
    $dbnames = $srv.Databases.name

      foreach ($dbn in $dbnames) {
        Write-Host $dbn
        if ($dbn -ne "master" -and $dbn -ne "model" -and $dbn -ne "msdb" -and $dbn -ne "tempdb") {
          $srv.DetachDatabase($dbn, $false)
       
          }
        }

    $dbfiles = New-Object System.Collections.Specialized.StringCollection

    $sqldatapath = "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\"

    $dbn = "sandbox"

    write-host "Instance: " $srv.Name
    write-host "Attach " $dbn

    $mdffiles = $sqldatapath + $dbn + ".mdf"
    write-host $mdffiles
    $ldffiles = $sqldatapath + $dbn + "_Log.ldf"
    write-host $ldffiles

    $dbfiles.Add($mdffiles)
    $dbfiles.Add($ldffiles)

    $srv.AttachDatabase($dbn, $dbfiles, "sa", "None")

    $dbfiles.Clear()

    #attach staging
    $dbn = "EncryptionPrimer"

    write-host "Instance: " $srv.Name
    write-host "Attach " $dbn

    $mdffiles = $sqldatapath + $dbn + ".mdf"
    write-host "MDF: " $mdffiles
    $ldffiles = $sqldatapath + $dbn + "_Log.ldf"
    write-host "LDF: " $ldffiles

    $dbfiles.Add($mdffiles)
    $dbfiles.Add($ldffiles)

    $srv.AttachDatabase($dbn, $dbfiles, "sa", "None")

    $dbfiles.Clear()

  • Allowing a User to Create Objects in a Schema

    I was testing something the other day and realized this was a security area I didn’t completely understand. I decided to write a few posts to help me understand the issues.

    I want to give a developer rights to create objects in a schema. In this case, I’ll stick with procedures, but the same thing would apply for tables, views, etc. How do I do this, allow someone to create objects in their schema?

    Let’s create a login and user:

    CREATE LOGIN steve WITH PASSWORD = ‘AR3allyStr0ng!P@**Wo9d’;
    GO
    USE Sandbox
    GO
    CREATE USER Steve FOR LOGIN Steve
    GO
    Now I have a user, and want them to be able to create this:

    SETUSER ‘Steve’;

    CREATE PROCEDURE Steve.MyProc
    AS
        SELECT
                1;
    RETURN

    If the user does this, they get:

    Msg 262, Level 14, State 18, Procedure MyProc, Line 3
    CREATE PROCEDURE permission denied in database ‘sandbox’.

    That’s no good.

    We can see from the error that we don’t have writes to create procedures. Let’s fix that. First, we change our context and then we grant permissions.

    SETUSER
    GO

    GRANT CREATE PROCEDURE TO Steve;

    GO

    With this done, let’s now try creating the procedure again with the SETUSER statement and the CREATE PROC statement. We then get:

    Msg 2760, Level 16, State 1, Procedure MyProc, Line 5
    The specified schema name "Steve" either does not exist or you do not have permission to use it.

    This didn’t used to be the case in SQL 2000, where schemas didn’t exist. Now we don’t have any implicit schema for our user. Let’s see if we can make anything.

    CREATE PROCEDURE MyProc
    AS
    SELECT 1;
    RETURN
    GO

    Returns this:

    Msg 2760, Level 16, State 1, Procedure MyProc, Line 11
    The specified schema name "dbo" either does not exist or you do not have permission to use it.

    At this point Steve doesn’t have permissions to any schema. Let’s start by adding a new schema.

    CREATE SCHEMA Steve
    GO

    Once this is done, can I now create a procedure?

    SETUSER ‘Steve’;

    CREATE PROCEDURE Steve.MyProc
    AS
        SELECT
                1;
    RETURN

    I get this:

    Msg 2760, Level 16, State 1, Procedure MyProc, Line 5
    The specified schema name "Steve" either does not exist or you do not have permission to use it.

    The same error as before. This makes perfect sense because although the schema exists, I don’t have permissions to use it.

    That’s the default in SQL Server. You don’t get any permissions by default. You need to explicitly set them.

    In this case, I want Steve to have control of the schema [Steve], so I really want the user, Steve, to own it. How do I do this?

    The key is that I want to use the Authorization clause with CREATE SCHEMA. I can’t use this with ALTER SCHEMA, only with CREATE SCHEMA.. so I need to do this:

    SETUSER
    GO
    DROP SCHEMA Steve;
    GO
    CREATE SCHEMA Steve AUTHORIZATION Steve;
    GO

    Once this is done, I can now let my user create procedures.

    SETUSER ‘Steve’
    GO
    CREATE PROCEDURE Steve.MyProc
    AS
    SELECT 1;
    RETURN
    GO
    This works, and my developer can work in their own schema. Of course I need to ensure the developer has access to other objects, hopefully using a role of some sort that I’ve created for my application users.

     

    SELECT SUSER_NAME();

    DROP SCHEMA Bob
    DROP SCHEMA steve

    REVOKE CREATE SCHEMA FROM Steve

    CREATE SCHEMA Steve AUTHORIZATION Steve

    ALTER SCHEMA Steve AUTHORIZATION Steve

    SETUSER ‘Steve’;
    SELECT SUSER_NAME();

    CREATE PROCEDURE Steve.MyProc
    AS
        SELECT
                1;
    RETURN

    CREATE PROCEDURE MyProc2
    AS
        SELECT
                1;
    RETURN

    SETUSER;
    SELECT SUSER_NAME();

    GRANT CREATE PROCEDURE TO Steve

    SETUSER
    DROP PROC steve.MyProc;
    DROP PROC steve.MyProc2;
    DROP SCHEMA Steve;

  • The Express Choice

    There are a number of editions of SQL Server, each of which has different capabilities, features, and restrictions. Over the years, the mix has changed, and it can get confusing for customers trying to decide what to purchase and use. Fortunately things seem to have become simpler the last few years, but you still have to make a few choices.

    The Express Edition has the most restrictions, including a database size restriction, but in many ways, it’s a very capable database server. It’s the evolution of the “desktop database”, MSDE, that was designed to take the place of Access for desktop software that needed a database.

    Recently I ran across a discussion on using Express in production, and I was surprised that many people didn’t think it was  a version capable of acting as a production server. It’s the same code base as the other versions of SQL Server, with more restrictions. This week, I wanted to see how most of you feel.

    Would you use Express Edition for a production database?

    I would. In fact, given the way licensing costs have soared for SQL Server, I’d be tempted to use Express in many places, especially for departmental sized applications. I wouldn’t care whether they were web based or client/server. As long as the database would remain below the 10GB limit and the 1GB RAM limitation didn’t kill performance, I think Express is a fine choice.

    Of course, outgrowing Express can be quite expensive and a shock for someone using it, but if you need a more powerful server, you need one. I just prefer to defer that cost if I can.

    Steve Jones

    The Voice of the DBA Podcast

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