Voice of the DBA

Tag: syndicated

  • SQL and Skiing – SQL Saturday #183

    This weekend of SQL Saturday #183 in Albuquerque, NM. It’s the first SQL Saturday in that state, and it’s a relatively short drive from Colorado. Six hours from my house, S of Denver, and a nice city. My brother used to live there, and I made a few trips to see him.

    I’m looking forward to going back, but not to tour the city. I’m sure that would be fun, but there’s a side diversion between Denver and Abuquerque:  Taos.

    Taos, NM has two ski resorts nearby: Taos Ski Valley and Angel Fire. I’ve never been to either of them, and I’d like to see what it’s like to ski in New Mexico. A few of my fellow Coloradans feel the same way and we’ve organized a bit of a caravan trip.

    Two or three of us are planning to drive Thursday night with two or three passengers, for a total of 5 or 6. We’ll ski Friday and then continue down to Albuquerque for the speaker event Friday night and SQL Saturday #183 on Saturday. A few of us will then go back to Taos on Saturday. Whether we will ski on Sunday depends on how these older bodies feel.

    I love the SQL Saturday events, and think they’re a great chance to teach people things, learn things, and give vendors a chance to meet potential customers face to face. However the networking is one of the best opportunities that I’ve seen from these events. The chance to meet other SQL Server professionals, find out what they are doing, learn about new features, functions, applications and opportunities is amazing.

    The chance to bond and make a new friend or acquaintance is one you shouldn’t pass up. You never know when you network might help you, and there are plenty of companies that view networks as more important than recruiters.

    I’m looking forward to some networking on the drive, getting to better know the people I’ve met at many local events. I’m also looking forward to speaking on Branding at the event and meeting even more SQL Server pros from New Mexico.

    If you get the chance to attend an event, it’s worth your time if you meet people and talk with them. I encourage you to try this in the future, especially at a SQL Saturday. Talk to people in sessions, between them, or even later at the after party.

  • Granting Permission to Grant Permissions

    I’ve never felt the need to allow this, but I saw someone ask the questions recently. Suppose you had a view and you wanted to allow a user to grant permissions to this view to other users.

    It’s not a weird edge case, but it’s just not one I’ve normally bothered with. I manage by groups, always, and never want a user to be granting permissions to a specific view. I inherently see users as fragile in the lifetime of an application (administrators as well)  and don’t like the idea of allowing them permissions.

    If you need to do this, however, there is a way. Let’s examine a basic scenario. I want to allow Bill to grant permissions on the view “MonthlySales”, which I’ve created in AdventureWorks. If I want to allow Bill to use this view, I need to do this:

    GRANT SELECT ON MonthlySales TO Bill

    If I log into the server as Bill and execute 

    SELECT * FROM MonthlySales

    I get this:

    grant1

    Simple stuff.

    However if I want Bill to be able to allow other people in his department to see this view, what can I do?

    If I examine the BOL page for GRANT, I see there is an option that can help here. The WITH GRANT option allows the person specified in the statement to grant the existing permissions to others.

    Let’s assume I have another user Sue. If I log in as Sue, and I execute the same SELECT that Bill ran above, I get this:

    Msg 229, Level 14, State 5, Line 1

    The SELECT permission was denied on the object ‘MONTHLYSALES’, database ‘AdventureWorks’, schema ‘dbo’.

    I could easily manage permissions as the DBA, and I prefer this, but for when you have some data that a user can manage, and you are in an environment where things change quickly, perhaps you want to delegate some permissions to users.

    Now, let’s change the initial permission I set up for Bill to this:

    GRANT SELECT ON MonthlySales TO Bill
 WITH GRANT OPTION

    Bill can still see the view and the data. However Bill can now run this:

    GRANT SELECT ON MonthlySales TO Sue

    Once he does this, Sue can see the view.

  • If You Need To Fix Database Filename Extensions

    In a recent post I showed how the file extension for a database doesn’t matter. It can be confusing, however, and you might wish to “fix” the filenames to conform to the proper extension. How can you do this?

    Well, to change a file name, or location, you need to take the database offline. This is noted in the Books Online Move Database procedure. Why? Well, the files need to be physically changed in the file system (either a rename or copy), so there is downtime here. Locations are one thing, but what about renames?

    The rename is simpler, and if you script this, downtime is minimal. The procedure is the same as listed in BOL:

    • set the database offline
    • rename the file
    • run the ALTER DATABASE command
    • set the database online

    This is pretty simple. We want to run this code:

    ALTER DATABASE [NameTest2] SET OFFLINE
GO
ALTER DATABASE [NameTest2]
 MODIFY FILE ( NAME = NameTest2
             , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest2.mdf' )
GO
ALTER DATABASE [NameTest2] SET ONLINE
GO

    However that code misses item #2 from above. I can manually perform that step, which is pretty easy, or I can script it if I allow xp_cmdshell changes. I know this is a security risk, but I can enable it and disable it all in the script:

    EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO 
ALTER DATABASE [NameTest2] SET OFFLINE
GO
EXEC xp_cmdshell 'rename C:\"Program Files"\"Microsoft SQL Server"\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest2.ldf nametest2.mdf'
GO
;
ALTER DATABASE [NameTest2]
 MODIFY FILE ( NAME = NameTest2
             , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\nametest2.mdf' )
GO
ALTER DATABASE [NameTest2] SET ONLINE
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO

    Note in here that I need some quotes in the RENAME command inside the shell so that Windows handles the spaces correctly in the path.

  • Full-Text Search – Thesaurus

    I would hope that most of us have used a thesaurus at some point in our careers. These allow us to substitute words for one another, providing for richer and more interesting communication.

    Full-text search in SQL Server includes a thesaurus that you can customize for your searches. As with the thesaurus some of us use when writing, this features allows the search engine to substitute one word for another in searches.

    You actually have to customize it. Here’s the default thesaurus for SQL Server 2008, which is stored in this location:

    <SQL_Server_data_files_path>\MSSQL11.MSSQLSERVER\MSSQL\FTDATA\

    If you look in this folder, you see a lot of XML files. These are the thesaurus files and they are named as tsxxx.xml, where xxx is the three letter language code. For English, the thesaurus is tseng.xml.

    ftsthesaurus1

    If I open up the English file, you can see there’s not much there in terms of entries.

    ftsthesaurus2

    This looks like the file that came with SQL Server 2005, if not SQL Server 2000. Note also that everything is commented out, and you need to remove these comment lines if you want to edit this file.

    The configuration isn’t that complex, but let’s look at a simple example. I’ll set up a small table and create a full text index on it.

    CREATE TABLE FTSTemp
( id INT
, Notes varchar(8000)
CONSTRAINT pk_ftstemp PRIMARY KEY (id)
)
;
GO
INSERT ftstemp SELECT 1, 'The quick brown fox jumped over the lazy dog'
INSERT ftstemp SELECT 2, 'I run WinXP.'

    I’m going to edit my thesaurus file to include these entries:

    <expansion>

        <sub>XP</sub>

        <sub>WinXP</sub>

    </expansion>

    And also

    <expansion>

        <sub>leaped</sub>

        <sub>jumped</sub>

    </expansion>

    I am only using expansion sets here. There are also replacement sets, but that’s for another post. In this case, when I search for any of the terms above, elements matching any other terms will be returned.

    NOTE: The US English file is tsenu.xml. The UK English file is tseng.xml.

    I’ll now create a full text index on this table, on the Notes column.

    I can issue this search, which I expect to work:

    SELECT 
  id
, notes
 FROM ftstemp
 WHERE CONTAINS(notes, 'quick')

    That returns the data I expect.

    Now to check the expansion set. To do that, I’ll need to use a FREETEXT query. Once I do this, I get results from both of my entries.

    ftsthesaurus4

    Note that if you edit the thesaurus, in order for your changes to show up in queries, you need to reload the Thesaurus file with this:

    EXEC sys.sp_fulltext_load_thesaurus_file 1033;

    The 1033 is for English. This is the LCID, which varies for each language.

    If you want to broaden your searches, include acronyms, etc, then the thesaurus is a good way to do this. Beware, however, that your entries will apply to all searches on the instance, so if you have disparate applications on the same instance, you might encounter some strange results.