Category Archives: SQL

All about Structured Query Language

Scripting SQL Logins, Windows Logins, group membership

I’m working with AlwaysOn Availability Groups in SQ’L 2014. One of the many interesting gotchas I have with AlwaysOn is that SQL Logins on each AG node must be created with identical SIDs, otherwise after failover the database users for those SQL Logins will be orphaned.

I discovered Bill Graziano’s post back from 2010 where he scripts out the SQL Logins (including SID and hashed password), plus the Windows logins and server role membership for SQL 2008 server. I have adapted it for SQL 2014 SP2.

Note the script was originally designed to script out the logins only if they do not exist already. I removed the ‘IF NOT EXISTS’ lines so it will script out all the SQL Logins and Windows Logins regardless of whether they exist.

USE [master]
GO

/* Object: UserDefinedFunction [dbo].[fn_hexadecimal] **/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_hexadecimal] 
(
 -- Add the parameters for the function here
 @binvalue varbinary(256)
)
RETURNS VARCHAR(256)
AS
BEGIN

 DECLARE @charvalue varchar(256)
 DECLARE @i int
 DECLARE @length int
 DECLARE @hexstring char(16)
 SELECT @charvalue = '0x'
 SELECT @i = 1
 SELECT @length = DATALENGTH (@binvalue)
 SELECT @hexstring = '0123456789ABCDEF' 
 WHILE (@i <= @length) 
 BEGIN
 DECLARE @tempint int
 DECLARE @firstint int
 DECLARE @secondint int
 SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
 SELECT @firstint = FLOOR(@tempint/16)
 SELECT @secondint = @tempint - (@firstint)
 SELECT @charvalue = @charvalue +
 SUBSTRING(@hexstring, @firstint+1, 1) +
 SUBSTRING(@hexstring, @secondint+1, 1)
 SELECT @i = @i + 1
 END
 return @charvalue

END
GO


SET NOCOUNT ON
GO
--use MASTER
GO
PRINT '-----------------------------------------------------------------------------'
PRINT '-- Script created on ' + CAST(GETDATE() AS VARCHAR(100))
PRINT '-----------------------------------------------------------------------------'
PRINT ''
PRINT '-----------------------------------------------------------------------------'
PRINT '-- Create the windows logins'
PRINT '-----------------------------------------------------------------------------'
SELECT 'CREATE LOGIN [' + [name] + '] FROM WINDOWS WITH DEFAULTDATABASE=[' + 
 default_database_name + '], DEFAULTLANGUAGE=[usenglish]
GO

'
FROM master.sys.server_principals
where type_desc In ('WINDOWS_GROUP', 'WINDOWS_LOGIN')
AND [name] not like 'BUILTIN%'
and [NAME] not like 'NT AUTHORITY%'
and [name] not like '%\SQLServer%'
GO

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Create the SQL Logins'
PRINT '-----------------------------------------------------------------------------'
select 'CREATE LOGIN [' + [name] + '] 
 WITH PASSWORD=' + [dbo].[fn_hexadecimal](password_hash) + ' HASHED,
 SID = ' + [dbo].[fn_hexadecimal](sid) + ', 
 DEFAULTDATABASE=[' + default_database_name + '], DEFAULTLANGUAGE=[usenglish], 
 CHECKEXPIRATION=' + CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECKPOLICY=OFF
GO
IF EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
 ALTER LOGIN [' + [name] + ']
 WITH CHECKEXPIRATION=' + 
 CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECKPOLICY=' + 
 CASE WHEN is_policy_checked = 1 THEN 'ON' ELSE 'OFF' END + '
GO


'
--[name], [sid] , passwordhash 
from master.sys.sql_logins 
where type_desc = 'SQL_LOGIN' 
and [name] not in ('sa', 'guest')

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Disable any logins'
PRINT '-----------------------------------------------------------------------------'
SELECT 'ALTER LOGIN [' + [name] + '] DISABLE
GO
' 
from master.sys.server_principals 
where is_disabled = 1

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Assign groups'
PRINT '-----------------------------------------------------------------------------'
select 
'EXEC master..spaddsrvrolemember @loginame = N''' + l.name + ''', @rolename = N''' + r.name + '''
GO

'
from master.sys.server_role_members rm
join master.sys.server_principals r on r.principal_id = rm.role_principal_id
join master.sys.server_principals l on l.principal_id = rm.member_principal_id
where l.[name] not in ('sa')
AND l.[name] not like 'BUILTIN%'
and l.[NAME] not like 'NT AUTHORITY%'
and l.[name] not like '%\SQLServer%'

Installing sqlcmd without SQL Server

I just ran into a need for sqlcmd without SQL Server. I’m on Windows Server 2016 Datacenter.

I installed Microsoft® ODBC Driver 11 for SQL Server – Windows, with filename  msodbcsql.msi

Installed from https://www.microsoft.com/en-us/download/details.aspx?id=36434

This is a dependency for Microsoft Command Line Utilities 11 for SQL Server, with filename MsSqlCmdLnUtils.msi

 I installed from https://www.microsoft.com/en-us/download/details.aspx?id=36433

I chose the 64-bit versions of both.

Restarted my console session and able to run sqlcmd without SQL Server!

My first book review! SQL Server 2014 with PowerShell v5 Cookbook

Has been a very busy year for me, haven’t posted lately, but was very excited to share this.

I’ve had the opportunity to be a technical reviewer for three PowerShell books this year!

One that I’ve really enjoyed reviewing has just been released, and the publisher Packt is offering an end of year sale for $5 eBook editions.

Donabel Santos (sqlbelle.com & @sqlbelle) is a wonderful blogger, author, trainer, and Microsoft MVP. This is her second PowerShell cookbook for SQL, and it’s an incredibly useful resource for learning the practical details of PowerShell, both in general and when working with SQL Server. All her examples come with code samples so you can leverage her scripts to get productive right away.

You can check out the book on the Packt website. It’s only $5 for a great PowerShell & SQL eBook, or $30 for the eBook and print version. It’s a phenomenal resource, and a great deal for DBAs looking to take the plunge into PowerShell, and DBAs familiar with PowerShell ready to expand their knowledge about all that’s possible with PowerShell in SQL.

Please let me know if you found her book useful, and have a Merry Christmas, Happy New Year, and enjoyable holiday season. 🙂

 

 

SQL 2016 WILL have HTML5 reports!

Very exciting news for Reporting Services users, who may have felt left out regarding all the new SQL features of late.

SQL 2016 will have a new rendering engine that will generate HTML5 reports!  You can provide feedback to Microsoft right from within SSRS development, so I imagine they will use that feedback to produce a lot of hotfixes and workarounds over the first few months after release.You can always switch back to the original rendering engine in a report if HTML5 doesn’t work out for you.

I’ll be interested to investigate how reports can be CSS-styled for functionality, for mobile presentation, and to coordinate with the  look and feel of the site in which they are presented.

More info on books online: https://msdn.microsoft.com/en-us/library/ms170438.aspx

SQL 2016 CTP2 is out, with Technet Virtual Labs ready to learn!

Microsoft just released SQL 2016 CTP 2. CTP is a customer technology preview, that lets us explore new features of the product before it’s released to marketing (RTM).

I found it in my MSDN new downloads.

Not ready to download and install it but still want to learn?

Technet Virtual Labs already has SIX virtual labs you can use to explore the new features!

SQL Server 2016 Virtual Labs!
SQL Server 2016 Virtual Labs!

Just search for SQL 2016

 

Scripting COMPLETE Full-Text indexes after a database restore

You may find yourself working with SQL 2000 databases, or later versions of SQL that were migrated from SQL 2000, with the Full-Text indexes stored outside the SQL data files.

In this scenario, once you restore a SQL Server database, your full-text catalogs and the full-text indexes within are not restored along with your data.

Note that this is not required for Full-Text indexes created in SQL 2005 and later, when the storage for Full-Text Catalogs was changed.

Right-Clicking and choosing to script the full-text catalog will NOT create the indexes within the catalog.

I found a useful script at humakhurshid.blog.com that scripts out the full-text catalog AND indexes. EDIT: However it misses some indexes, I found a better script at the bottom of the comment thread at this blog at blog.strictly-software.com

This script at mikesdatawork.wordpress.com will rebuild the full-text catalogs for all those newly created full-text indexes. This may be a useful maintenance plan script as well.

I had full-text catalogs with names that include spaces (FT Index) so I had to modify it slightly (added these brackets: [” + sftc.name + ”] ) to work.

Of course now those indexes need to populate. Found this stackoverflow post that gives a solution for one database, here’s my query to check population status for all catalogs.

 

--Check all fulltext catalogs on server. Ok to run as a single line.
--1 means populating, 0 means idle
 
EXEC sp_MSForEachDB 'SELECT FULLTEXTCATALOGPROPERTY(cat.name,''PopulateStatus'') as [?] FROM [?].sys.fulltext_catalogs AS cat'

 

Off to SQLPASS 2013!

SQL PASS Summit 2013

This year I’m volunteering at SQL PASS Summit 2013 as an MCT Volunteer, to assist those interested in certifying for SQL Server 2012.  Being an MCT means keeping up with certifications, which means keeping up with tests, and since 2002 I’ve certified on SQL 2000,2005 and 2008.

This May I achieved my MCSE: Data Platform this year, and working toward MCSE: Business Intelligence certification as well, with just one more test to go!

If you’re nervous about taking a Microsoft exam, just know you can do it, like thousands of other have. If you want help preparing to take a SQL Server exam, can you imagine a better place than the PASS Summit with thousands of smart SQL people there to help? Check out the PASS info page on MCP certification resources, like study hall, exam prep sessions,  practice exercises, and more.

I’m looking forward to meeting SQL people and absorbing the knowledge.  Also interested in exploring Charlotte and investigating the craft beer scene 🙂  Any one heading out there? I’ll buy the first round.