Voice of the DBA

Category: Blog

  • Let NonAdmins Get Logins

    I saw a question recently about how to allow some users to see the logins on a server without being a sysadmin or securityadmin. This was in support of a migration effort, so users needed read rights without being able to change anything.

    This appears to be a good place to use the WITH EXECUTE AS option for a stored procedure. I decided to try. I have a normal, non privileged user, JoeDBA, with rights to connect to my Sandbox database. I decided to create a proc as a sysadmin user.

    Here is my code. Notice the WITH EXECUTE AS option. Since I created this with my sjones, sysadmin, account. This should be able to call into master and get the data.

    CREATE PROCEDURE GetLogins_NonSA
    WITH EXECUTE AS OWNER
    /*
    Description:

    Changes:
    Date       Who         Notes
    ———- —         —————————————————
    1/24/2017  PLATO\Steve Initial proc to get server logins
    */
    AS
    BEGIN
    SELECT name
         , principal_id
         , sid
         , type
         , type_desc
         , is_disabled
         , default_database_name
    FROM master.sys.server_principals

    RETURN
    END
    GO

    GRANT EXECUTE ON GetLogins_NonSA TO MigrationRole
    go
    ALTER ROLE MigrationRole ADD MEMBER JoeDBA

    Now, I can log in with a low privileged user. By default, a query against sys.server_principals should only return my login. If I query the DMV, I get this:

    2017-01-24 10_08_56-SQLQuery2.sql - (local)_SQL2016.sandbox (JoeDev (71))_ - Microsoft SQL Server Ma

    Now, I can execute the procedure. I’ve scrolled the results a bit, but you can see I view other users.

    2017-01-24 10_09_30-SQLQuery2.sql - (local)_SQL2016.sandbox (JoeDev (71))_ - Microsoft SQL Server Ma

    Certainly I could limit the columns returned, or transform them to provide more data, but this is a good way to give read-only access to a login about other logins.

  • Get Your SQL Server Product Key with Powershell #dbatools

    I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

    There are lots of tasks that are easily accomplished with both PowerShell and T-SQL.

    UPDATE: The cmdlet was renamed to Get-DbaProductKey

    Get-SqlServerKey is a command that will find your product keys. It’s not often you might need this, but it’s a pain to track them down from the registry and be sure you get them for all your instances.

    Here’s the view from one of my development machines:

    2017-01-27 15_31_08-powershell

    There is a limitation here that appears to have issues with SQL Server 2016, but it’s still helpful for other versions. You can also specify a list of servers in a file for this to use, as well as using credentials to connect.

    A quick, handy item, and one that should make auditing (pre-2016) SQL Servers easy.

  • Using a DMK in TDE

    When you setup TDE, you need to create a DMK (Database Master Key) in the master database if one doesn’t exist. This key forms the basis of a chain of protection for your data inside of the SQL Server instance. The hierarchy is:

    • The DMK exists in the master database. This protects a certificate or asymmetric key by encrypting it.
    • The certificate in turn encrypts the DEK in a database using TDE. This certificate is required to open the DEK in the database, or open the DEK during a restore operation.
    • The DEK encrypts the data.

    There is only one DMK in each database, so the DMK in master for TDE could be used to protect multiple certificates, asymmetric keys, or symmetric keys that exist in master for use by TDE or any other encryption mechanism.

    A particular DMK is not needed to restore a TDE database on another instance. Rather the new instance of SQL Server should have a DMK created in the master database. Then the backup of the certificate is restored on the other instance, protected by the DMK there. Once this is complete, the TDE database can be restored.

    If you’d like to know more about what a DMK is, I’ve written about it in a previous post.

  • Speaking at SQL Konferenz 2017

    I’m heading back across the Atlantic this February for SQL Konferenz. This is an intimate SQL Server event in Darmstadt (near Frankfurt). I was fortunate to be picked to speak in 2015, but skipped 2016 as the timing wasn’t good. This year, things lined up for me, and I’ll be attending the conference after a short visit to the Redgate offices.

    I will be presenting a talk on Database Devops, updated from last year, and looking at ways in which you can get your database into a better software development process.

    There are a number of other great sessions on all aspects of SQL Server, both the core engine and BI topics. There are also a good selection of presentations in both English and German. Just for fun, I might go to a German one to see if I can follow along. Perhaps I’ll go see the Cloud Service in Azure one.

    You can register today for SQL Konferenz and come enjoy the event with me. See you in February.