Skip to main content


Showing posts from January, 2014

Script to show all database and server roles and their members

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 database role membership, amen…

An easier way to deal with sp_who2

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 IFEXISTS(SELECT1FROMtempdb..sysobjectsWHEREid=OBJECT_ID('tempdb..#spwho2'))DROPTABLE#spwho2 CREATETABLE#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(100) ,[SPID2]INT ,[RequestID]INT ) INSERTINTO#spwho2 EXECsp_who2
SELECT[SPID] ,[Status] ,[Login] ,[HostName] ,[BlkBy] ,[DBName] ,[Command] ,[CPUTime] ,[DiskIO] ,[LastBatch] ,[ProgramName] ,[SPID2] ,[RequestID]

How to stop Internet Explorer prompting for username and password with SSRS

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.

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 local intranet it is a matter of addin…