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:

Thursday, October 27, 2016

Rename a SQL Server database AND its data and log files

Just renamed a database for a customer, and I wanted to share the logic with all of you. When we rename a database, it only effects the database name itself.  It does not change the data or log file names. I HATE it when the data/log file names don't match the db name! Consistently named objects are faaaar easier to manage, imo.  :-)  You can use this method to rename the database AND its data and log files.  Easy peasy.  In this example, the 'InformDVODV' database is renamed to 'InformDVOPD'.

-- get your db filenames
USE InformDVODV; -- current database name
EXEC sp_helpfile

-- physical names and paths
-- only change the data/log file names in the FILENAME value
ALTER DATABASE InformDVODV -- don't change
MODIFY FILE (
       NAME = 'InformDVODV', -- don't change
       FILENAME = 'E:\MSSQL\DATA\InformDVOPD.mdf' -- change this
);
ALTER DATABASE InformDVODV -- don't change
MODIFY FILE (
       NAME = 'InformDVODV_log', -- don't change
       FILENAME = 'O:\MSSQL\Log\InformDVOPD_log.ldf' -- change this
);

-- logical names
-- only change the NEWNAME value
ALTER DATABASE InformDVODV MODIFY FILE (
NAME = InformDVODV,
NEWNAME = InformDVOPD -- change this
);
ALTER DATABASE InformDVODV MODIFY FILE (
NAME = InformDVODV_log,
NEWNAME = InformDVOPD_log -- change this
);

-- now we rename the database
USE master; 
GO 
ALTER DATABASE InformDVODV 
Modify Name = InformDVOPD; 
GO 

-- check your newly renamed files
USE InformDVOPD; -- new db name
EXEC sp_helpfile


That's it!  Important to know, you cannot do this if users are in the database.  If there are active connections, you will need to kill them before attempting the rename. Run this to kill any connections by setting the database to single user mode:

USE [master];
GO
-- disconnect all existing sessions 
ALTER DATABASE InformDVODV SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

 And you'll run this after you've renamed the database:

-- change to multi-user
ALTER DATABASE InformDVOPD SET MULTI_USER
GO


That's it!  Follow the sequence and double-check your filenames, this will work fine. I would recommend taking a look at both of these for more details as well:

ALTER DATABASE

RENAME DATABASE

Monday, October 24, 2016

How to query the Windows domain name for SQL Server

Yes.  There will come a time when you need to confirm the domain name of your server. Today, for example, I was setting up a bunch of RDP connections, and the domain name was a required credential.  This particular customer has several domains, so I just wanted to check quickly, to be sure I was targeting the right one.  I have posted 3 ways below, beginning with the easiest... which is the one I prefer to use!  Take a look and let me know if you have another way to do the same.

-- 1. Easiest
SELECT DEFAULT_DOMAIN() DomainName

-- 2. Also easy, but calling an xp
EXEC master..xp_loginconfig 'Default Domain'

-- 3. Way overkill, but it works
DECLARE @Domain VARCHAR(100), @key VARCHAR(100)
SET @key = 'system\controlset001\services\tcpip\parameters\'
EXEC master..xp_regread
      @rootkey = 'HKEY_LOCAL_MACHINE',
      @key = @key,
      @value_name = 'Domain',
      @value = @Domain OUTPUT

      SELECT @Domain [DomainName]


Just a quick tip, but I know that some of you will find it helpful.  

Friday, September 2, 2016

Check the SQL Server Database Compatibility Level

Just a quick script to check your database compatibility level... and change it, if you need to.  This statement will bring back the compatibility levels for each database in the system:

     /* query the db compatibility level */
     SELECT
          name [database],
          compatibility_level ,
          [version] =
          CASE compatibility_level
             WHEN 65  THEN 'SQL Server 6.5'
             WHEN 70  THEN 'SQL Server 7.0'
             WHEN 80  THEN 'SQL Server 2000'
             WHEN 90  THEN 'SQL Server 2005'
             WHEN 100 THEN 'SQL Server 2008/R2'
             WHEN 110 THEN 'SQL Server 2012'
             WHEN 120 THEN 'SQL Server 2014'
             WHEN 130 THEN 'SQL Server 2016' END
     FROM
          sys.databases;
     
And when you need to change it:

     /* change db compatibility level */
     ALTER DATABASE YourDatbaseName
     SET COMPATIBILITY_LEVEL = 110;

Be wary, though, about changing the compatibility level.  Often times by doing so, you will be unable to take advantage of certain features. Take a look at this MSDN reference for more details on the differences between higher/lower compatibility levels:     

Friday, July 29, 2016

Using a SQL Server Logon Trigger

One of my customers has an instance where many people log in as sa. We're planning to change the password and rename the sa login, but we first need to know who is using it, and for what. To do this, I've put together an sa Logon Trigger. While I typically avoid triggers, I believe this one is very manageable because it is specific to only one login, and it is only collecting details from each login attempt.

First I create a LogonAudit table to collect the logon details.

     USE DBA;
     CREATE TABLE LogonAudit
     (
      LogonTime datetime,
      HostName varchar(50),
      ProgramName varchar(500),
      LoginName varchar(50),
      OriginalLoginName varchar(50),
      ClientHost varchar(50)
     ) ON [DBA_Data]
    GO

Now for the trigger.

CREATE TRIGGER tr_LogonTrigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
/* 
Used with 'sa logon audit', to determine who is logging in with sa, when and from where.
Auth:  Me
Date:  7/28/2016  
*/
BEGIN
    DECLARE
@LogonTriggerData xml,
       @EventTime datetime,
       @LoginName varchar(50),
       @ClientHost varchar(50),
       @LoginType varchar(50),
       @HostName varchar(50),
       @AppName varchar(500)

  SET @LogonTriggerData = eventdata()
  SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
  SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
  SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
  SET @HostName = HOST_NAME()
  SET @AppName = APP_NAME()

  --- Add condition to log only succesful sa logins
  IF((ORIGINAL_LOGIN() = 'sa'))
  INSERT INTO DBA..LogonAudit (
       LogonTime,HostName,ProgramName,LoginName,OriginalLoginName,ClientHost )
  SELECT
       @EventTime,
       @HostName,
       @AppName,
       @LoginName,
       ORIGINAL_LOGIN(),
       @ClientHost
END

GO


How to test it?  Easy. Launch SSMS and open a query window.  Look at the lower right, you should see you're logged with your domain login.  Right click the query screen, choose the 'Connection' option, and then 'Change Connection...'.  Here you will change the authentication to 'SQL Server Authentication', then input the sa login and password and hit 'Connect'.  That's it!  That single action was a logon attempt by the sa login, and it should have triggered an insert into your LogonAudit table.  

This is what I captured in my local instance:


















But wait.  We logged in one time, and we have more than one entry recorded in our LogonAudit table.  Why?  This happens because there are multiple SQL Server Services running in parallel.  If you were to go in and stop all SQL Server Services except for SQL Server (MSSQLSERVER), then you would only capture one entry in the table for each login attempt.

Maybe you want to disable it for some reason?  Rather than deleting it, you can just DISABLE/ENABLE with these statements:

     -- DISABLE TRIGGER
     DISABLE TRIGGER tr_LogonTrigger ON ALL SERVER
     -- RE-ENABLE TRIGGER
     ENABLE TRIGGER tr_LogonTrigger ON ALL SERVER

See more details here regarding the SQL Server Logon Trigger:
    https://msdn.microsoft.com/en-us/library/bb326598(v=sql.110).aspx