Category Archives: Uncategorized

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%'

Install SQL Express 2014 or 2016 from PowerShell

This may be useful for some folks out there. While the documentation on SQL Books online for command line installs is extensive, the examples there are limited, and the troubleshooting steps are not described clearly.

You will need to download the SQL installer iso or files and mount or extract them, and locate the setup.exe.

For SQL 2014 Take note that:

  1. This is installing SQL Express, a /FEATURES parameter was not required. Changes will be required if using a different distribution.
  2. This creates a default instance of SQL, change MSSQLSERVER to your desired instance name.
  3. Local Windows administrators will have SQL access by default
  4. The line of PowerShell code below that defines $params has no carriage returns between the single quotes.
  5. The results of a successful or failed install can be found in the folder: “$env:ProgramFiles\Microsoft SQL Server\###\Setup Bootstrap”, where ### is 120 for SQL 2014, 130 for SQL 2016.
#Set-Location to where your SQL Setup.exe is located, then run:
$params = @'
/ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /QS /HIDECONSOLE /INDICATEPROGRESS="True" /IAcceptSQLServerLicenseTerms /SQLSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE" /SQLSYSADMINACCOUNTS="builtin\administrators" /SKIPRULES="RebootRequiredCheck" 
'@ 
Start-process .\setup.exe $params -wait

For SQL 2016, I’m experimenting with SQL 2016 Enterprise Evaluation, and this worked as well:

#Set-Location to where your SQL Setup.exe is located, then run:
$params = @'
/ACTION=Install /QS /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /HIDECONSOLE /INDICATEPROGRESS="True" /IAcceptSQLServerLicenseTerms /SQLSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE" /SQLSYSADMINACCOUNTS="builtin\administrators" /SKIPRULES="RebootRequiredCheck" 
'@ 
Start-process .\setup.exe $params -wait

Presenting PowerShell for the DBA, Part 2 – Objects and Tools – for the PASS DBA Virtual Chapter

Wednesday March 8, 2017 will be presenting online  for the PASS DBA Virtual Chapter.

The topic is PowerShell for the DBA, Part 2 – Objects and Tools

If you’d like to join us, here is the attendee link!

Here’s the link to part 1 of the talk, including videodownloadable scripts, and resources to learn more.

Link to part 2 content on the PASS DBA Virtual Group – Meeting Archive page.

Link to the event page, with video and zip of demo scripts.

Hope to see you there!

Presenting online for PASS DBA Virtual Chapter

I’ll be presenting on POWERSHELL FOR THE DBA  to the PASS DBA Virtual Chapter

Sept 28, 2016, Noon Eastern Time

Abstract: This webinar introduces the SQL DBA to PowerShell and how it can be used to automate many common SQL Server tasks and monitor SQL Server metrics. You will be shown the basics of PowerShell and see examples of using it to automate and monitor SQL Server more efficiently, utilizing freely available SQL PowerShell modules, including the newly available SQLServer PowerShell module released with SQL Server Management Studio 2016, and other community SQL modules.

Registration link is here:

https://attendee.gotowebinar.com/register/7552966861613110786

Will share my powershell demo scripts and recommended PowerShell resources on my PowerShell and SQL presentation page.

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

Ignite 2015 in Chicago! Keeping track of sessions in Google calendar

I’m lucky and honored to be volunteering this year with the Hand On Labs team here at Ignite 2015 in Chicago. Hope to blog about the amazing lab environment in a future post.

I’m catching as many sessions as I can, and wanted to import my Ignite schedule into Google calendar on my phone. The kind folks at Ignite provided a help document for exporting the My Ignite schedule into various calendars, but the steps for Google Calendar were not clear to me, so perhaps this will help someone else attending Ignite this year.

1.Log into your My Ignite site. Click on My Schedule.

2.Right-Click the Export Calendar link, and copy the URL.
Ignite schedule to google calendar, step 1

3.Paste it into notepad, and replace webcal with https. Copy to clipboard again.

Ignite schedule to google calendar, step 2

4. Open Google Calendar, click the triangle by ‘Other calendars’ and choose Add by URL.Paste the link there, and click Add to Calendar button.
Ignite schedule to google calendar, step 3

5. Events imported, though I’m not sure how I’ll attend 5 sessions at one time…

Ignite schedule to google calendar, step 4

Let me know if this helped you, and enjoy the conference!

Presented on SQL 2012/2014 – What You Need to Know

Yesterday New Horizons in Fort Lauderdale asked me to present to some of their customers on SQL 2012 and and 2014, focusing in on the new features most essential to know. You can find the slides in the Presentations link above.

The presentation was only 3 hours, so I really struggled with what to cover, and I boil down the most important features to these:

  1. AlwaysOn in SQL 2012/2014
  2. Clustered ColumnStore Indexes in SQL 2014 (2012 has Non Clustered ColumnStore Indexes only)
  3. In-Memory OLTP in SQL 2014

Yes, I talked about DQS,MDS,Tabular,SSIS Project Model, T-SQL Enhancements, Azure options, and some other cool features. But the three above are, in my opinion, the most important.

Disagree with me? Let me know your opinion in a comment or by contacting me!