Voice of the DBA

Category: Blog

  • The Foundry and Data Masking

    There’s a group at Redgate that investigates new ideas and products. They’re called the Foundry, and they do some cool things. One of those is work on data masking. They’ve got a whole section on the Redgate site. Check it out and see what you think.

    I’ve seen some of their early work in other areas over the years, and it can be interesting to think about future ideas. Some of our products have come out of research, so I’m always looking to see what they’re up to.

    I think data masking is a very useful idea, and you can check out some of what they’re thinking now. It will be interesting to see what comes out of this.

  • Opening the PowerShell ISE from Explorer

    This is a cool productivity trip, and one that I ran into by accident. I had heard that I could launch a command prompt by typing cmd in the address. That works, and it’s cool. It even works with ConEmu, which is my default command window.

    However, while I was looking for other hints, I found that Powershell_ISE also works. That will launch the Powershell_ISE editor with the current folder as the default one in the lower pane. The “open” dialog, is still a the previous location, but I’m not sure how easy that is to change.

    In any case, here’s how this works. Browse to a folder in Exporer, such as a GitHub repo I have:

    2017-01-06 12_13_47-Load-SQL-Saturday-Sessions

    Type “powershell_ise”

    2017-01-06 12_13_56-Load-SQL-Saturday-Sessions

    Hit Enter, and the ISE appears. Note the path in the lower pane.

    2017-01-06 12_14_18-Windows PowerShell ISE

    That’s it.

    Quick, easy, and if you want to get to the ISE quickly to do something, or write some code, this is one way to do from a folder.

  • 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.