SQL Server Snippets | Paul Hewson

From time to time I am asked to provide an "access" list for a database or server. I have various scripts lying around the place and I cobble something together as needed but I have never got around to putting something comprehensive together until today. Sure, you can Google for a solution to save the bother of having to type, but in doing so I never found something that did exactly what I wanted. Today I found something that got me started by a chap called Mangal Pardeshi but there were a couple of problems with it, 1) it didn't like databases with - or _ in the name and 2) it only covered database roles and not server roles. So with thanks to Mangal, I have reworked his stored procedure (so it is just a query) and you can use it below (with the usual caveats): /******************************************************************************************** Name    : dbo.List_DBRoles.sql Author  : Mangal Pardeshi / Paul Hewson Purpose : Show all databas...

As a DBA sp_who2 is a stored procedure I use on a daily basis, however, it is not possible to filter its output or manipulate it in any way. To help with this I use the simple script below as a snippet that exports the output of sp_who2 to a temporary table where I can play with the results much easier. -- Export sp_who2 to temp table IF EXISTS ( SELECT   1   FROM tempdb .. sysobjects WHERE id = OBJECT_ID ( 'tempdb..#spwho2' )) DROP TABLE #spwho2 CREATE TABLE #spwho2 (     [SPID] INT     , [Status] VARCHAR ( 30 )     , [Login] VARCHAR ( 50 )     , [HostName] VARCHAR ( 30 )     , [BlkBy] VARCHAR ( 30 )     , [DBName] VARCHAR ( 30 )     , [Command] VARCHAR ( 50 )     , [CPUTime] INT     , [DiskIO] INT     , [LastBatch] VARCHAR ( 30 )     , [ProgramName] VARCHAR ( 1...

Stopping Internet Explorer prompting for username and password when you connect to SQL Server Reporting Services (when the SSRS server  is on the local Intranet (hint, hint)) could be one of those problems that you never end up resolving. No matter what you change, every time you close IE and open it again it prompts you to log in. This can drive you, and possibly more importantly your customers, around the bend. Click to enbiggen For information we are running SSRS 2012 with SSL enabled, browsing to a FQDN, eg. https://reportserver01.domain.local/Reports/ using IE 8 or 9. There are so many web pages that deal with this and any one of them could work for you - it's a matter of trial and error working through the suggestions. I had this issue today  but against all odds I managed to resolve it. Turns out to be pretty simple but it may not work for you. If not there are some links at the bottom of this post that may or may not help. If the SSRS server is on your lo...