Monday, January 23, 2017

How do I change the db owner for a lot of databases?

Good question.  Executing sp_changedbowner a few times is not that big of a deal, but it can become a bit tedious if you're changing it for 10, 20 or 36 databases -- like I did for a customer today.  Just a short post, but I wanted to share it with you.  

You may know, sp_changedbowner has been deprecated.  It's still there, but it will be pulled out in a future release.  The replacement is ALTER AUTHORIZATION, which is what I have used in this post.  Check it out, let me know what you think.

Because there were so many to change, the first thing I did was to write them all into a temp table. This way, we would be able to go back quickly and correct matters, if needed. 

SELECT name [database],suser_sname(owner_sid) [owner]
INTO DBA.dbo.DatabasesWithOwners
FROM sys.databases
WHERE suser_sname(owner_sid) <> 'sa'

Just a safety belt, but your table will be loaded like this:





















For the next step, you'd run this against that table you just created -- or against sys.databases, if you did not create the temp table, like this:

SELECT 'ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(name) + ' TO [sa];'
FROM sys.databases
WHERE suser_sname(owner_sid) <> 'sa' 

You'll get something back like this:













Cut and paste that out into a new query window, and that's that.  The one real quandary, as I told my customer, is knowing whether you have any applications out there that actually depend upon that database ownership belonging to whatever domain user it was previously owned by.  That is why I created the above temp table... and also why it is not recommended to use a domain user's login for database ownership.  But that is another discussion.

Until next time, please take a look at this for more information on ALTER AUTHORIZATION:

    https://technet.microsoft.com/en-us/library/ms187359(v=sql.105).aspx























Thursday, January 5, 2017

How do I know if Full-Text Search is installed?

One of my customers asked me this question yesterday. Is SQL Server Full-Text search installed? Any way to know if it's being used?  This is what I told him.

If this first SELECT statement returns 1 on any of your SQL Server instances, that tells us that Full-Text Search was included in the installation.

          SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')

That only means it was installed.  It does not mean that it is in use.  This next query will look for fulltext catalogs in any of your user databases.  If it finds a catalog, then we could say that it is being used, or at least that it was used at some point in time.  If nothing is returned, that means it was never put to use.


-- create temp table
CREATE TABLE #fulltextinfo (
  DatabaseName VARCHAR(128),
  [FulltextCatalogName] VARCHAR(128));

-- check each db
INSERT #fulltextinfo (DatabaseName,FulltextCatalogName)
EXEC sp_MSforeachdb 'USE ? SELECT ''?'', name FROM sys.fulltext_catalogs;'

-- see if we found any
SELECT * FROM #fulltextinfo

-- cleanup temp table
DROP TABLE #fulltextinfo;



That's pretty much it, though I'd encourage you take a look at this reference for much more detail on sys.fulltext_catalogs: