Rodney Landrum demonstrates an ingenious way to monitor blocked SQL Server processes across all your servers, using nothing but SSRS in his posting "The Reporting Services 3-Trick Pony"

This weblog is sponsored by FITS.
We support PASS Germany!

If you would like to split values from a column which are divideable beacause they are using the same delimiter like an ',' for example you could use the function below. The function also brings up the postion, which sometimes could be very usefull, too.

To call the function just use,

          select * from dbo.fn_split('Value
1,Value 2',',')

the first parameter is the column to split, the second contains the delimiter. You can not use different delimiters at one time at the moment, if you want to suggest some enhancements, please use the comments.... :-)
The statement will bring up the following result set:

          CREATE
          FUNCTION [dbo].[fn_Split](@text
nvarchar(max), @delimiter char(1)
= ' ') RETURNS @Strings TABLE ( positionintIDENTITYPRIMARYKEY, value nvarchar(max)
) ASBEGINDECLARE @indexintSET @index =
-1 WHILE (LEN(@text) > 0) BEGINSET @index =
CHARINDEX(@delimiter , @text) IF (@index =
0) AND (LEN(@text) > 0) BEGIN INSERT INTO @Strings VALUES (@text) BREAKENDIF (@index >
1) BEGIN INSERT INTO @Strings VALUES (LEFT(@text,
@index - 1)) SET @text = RIGHT(@text,
(LEN(@text) - @index)) ENDELSESET @text
= RIGHT(@text, (LEN(@text) - @index)) ENDRETURN END

This weblog is sponsored by FITS.
We support PASS Germany!

Are you also always looking in the BOL if you have to deal with datetime?

This stored procedure could bring some light in the dark...

You will have to call it with the following statement:

          Select dbo.fn_formatdate(getdate(),'formated
date: \TT\MM\YYYY \hh:\mi:\ss \ms Week: \wk Weekday: \DW Quarter: \qq'),getdate()

The formatstring can contain severall formatstrings and will bring up all datetimeparts you want, including some additional textparts...

          CREATE
          FUNCTION [dbo].[fn_formatdate](@dat
datetime,@format varchar(255)) RETURNSvarchar(255) ASBEGINdeclare @ret varchar(255) declare @i int--
year 4 digitsset @ret=replace(@format,'\jjjj',cast(year(@dat) asvarchar(4))) --
year 4 digits set @ret=replace(@ret,'\yyyy',cast(year(@dat) asvarchar(4))) --
day 2 digits set @ret=replace(@ret,'\dd',right('0'+cast(day(@dat) asvarchar(2)),2)) --
weekday 1 digit set @ret=replace(@ret,'\dw',cast(datepart(dw,@dat) asvarchar(1))) --
day of yearset @ret=replace(@ret,'\dy',right('00'+cast(day(@dat) asvarchar(3)),3)) --
hour 2 digitsset @ret=replace(@ret,'\hh',right('0'+cast(datepart(hh,@dat) asvarchar(2)),2)) --
year 2 digits set @ret=replace(@ret,'\jj',right(cast(year(@dat) asvarchar(4)),2)) --
minute 2 digits set @ret=replace(@ret,'\mi',right('0'+cast(datepart(mi,@dat) asvarchar(2)),2)) --
month 2 digitsset @ret=replace(@ret,'\mm',right('0'+cast(month(@dat) asvarchar(2)),2)) --
milliseconds 3 digitsset @ret=replace(@ret,'\ms',right('00'+cast(datepart(ss,@dat) asvarchar(3)),3)) --
quarter 2 digits set @ret=replace(@ret,'\qq',right('0'+cast(datepart(qq,@dat) asvarchar(2)),2)) --
seconds 2 digitsset @ret=replace(@ret,'\ss',right('0'+cast(datepart(ss,@dat) asvarchar(2)),2)) --
day 2 digitsset @ret=replace(@ret,'\tt',right('0'+cast(day(@dat) asvarchar(2)),2)) --
week 2 digitsset @ret=replace(@ret,'\wk',right('0'+cast(datepart(wk,@dat) asvarchar(2)),2)) --
year 2 digitsset @ret=replace(@ret,'\yy',right(cast(year(@dat) asvarchar(4)),2)) --
day 1-2 digits set @ret=replace(@ret,'\d',cast(day(@dat) asvarchar(2))) --
hour 1-2 digits set @ret=replace(@ret,'\h',cast(datepart(hh,@dat) asvarchar(2))) --
month 1-2 digits set @ret=replace(@ret,'\m',cast(month(@dat) asvarchar(2))) --
minute 2 digitsset @ret=replace(@ret,'\n',right('0'+cast(datepart(mi,@dat) asvarchar(2)),2)) --
quarter 1 digit set @ret=replace(@ret,'\q',cast(datepart(qq,@dat) asvarchar(1))) --
seconds 1-2 digitsset @ret=replace(@ret,'\s',cast(datepart(ss,@dat) asvarchar(2))) --
day 1-2 digits set @ret=replace(@ret,'\t',cast(day(@dat) asvarchar(2))) --
week 1-2 digits set @ret=replace(@ret,'\w',cast(datepart(wk,@dat) asvarchar(2))) return @ret END GO

This weblog is sponsored by FITS.
We support PASS Germany!

Do you want to create database snapshots? And do you want to do it programmatically? Then you could use this stored procedure...

Enjoy!

          CREATE
          PROC [dbo].[spr_CreateSnapshot]
(@Masterdb VARCHAR(255), @SnapshotName VARCHAR(255),
@ExecuteBIT = 1) ASSET NOCOUNT ONDECLARE @NewLine CHAR(2) DECLARE @Q CHAR(1) DECLARE @fname VARCHAR(255) DECLARE @extention VARCHAR(255) DECLARE @Pfad VARCHAR(255) DECLARE @DBname VARCHAR(255) DECLARE @LogicName VARCHAR(255) DECLARE @Command VARCHAR(MAX) DECLARE @indexExt INTDECLARE @indexPfad INTDECLARE @lenFname INTDECLARE @lenPfad INTDECLARE @lenDB INTDECLARE @lenExt INTCREATETABLE #Info
(physical_name VARCHAR(255) notnull,
logicname VARCHAR(255) notnull) SET @newLine
= CHAR(13) + CHAR(10) SET @Q
= CHAR(39) SET @command = 'INSERT
INTO #info (physical_name, logicname) SELECT s.physical_name,s.[name] AS LogicName
FROM ' + Quotename(@masterdb) + '.sys.filegroups as g INNER
JOIN sys.master_files AS s ON s.type = 0 AND s.database_id = db_id(' + @Q +
@Masterdb + @Q + ') AND s.drop_lsn is null AND s.data_space_id =
g.data_space_id ORDER BY g.data_space_id'EXECUTE (@Command) SET @Command
= 'CREATE DATABASE ' + @SnapshotName + @NewLine SET @Command
= @Command + 'ON' + @NewLine DECLARE c CURSOR READ_ONLY FORSELECT physical_name,
logicname FROM #info OPEN c FETCHNEXTFROM c INTO @fname,@LogicName WHILE (@@fetch_status
<> -1) BEGINIF (@@fetch_status
<> -2) BEGINSET @fname
= REVERSE(@fname) SET @lenFname = LEN(@fname) SET @indexExt
= CHARINDEX('.',@fname) -1 SET @indexPfad
= CHARINDEX('\',@fname) - 1 SET @extention
= REVERSE(SUBSTRING (@fname, 1, @indexExt)) SET @lenExt
= LEN(@extention) SET @Pfad = LEFT (REVERSE(@fname),
@lenFname - @indexPfad) SET @lenPfad = LEN(@Pfad) SET @DBname
= SUBSTRING(REVERSE(@fname), @lenPfad + 1, (@lenFname -
@lenPfad - @lenExt) - 1) SET @Command = @Command + '(Name
= ' + @Q + @LogicName + @Q + ', Filename = ' + @Q SET @Command
= @Command + @Pfad + @SnapshotName + '_' + @DBname + '.' + 'ssh' +
@Q + '),' + @NewLine ENDFETCHNEXTFROM c INTO @fname,@LogicName ENDCLOSE c DEALLOCATE c SET @Command
= LEFT(@Command,LEN(@Command)-3) + @NewLine + 'AS
SNAPSHOT OF ' + @masterdb IF @Execute =
1 BEGINEXEC (@Command) ENDELSEBEGINSELECT @Command AS Command END

This weblog is sponsored by FITS.
We support PASS Germany!

You are interested in getting some informations about your SQL Server, for example to be able to do an auditing on your SQL Server via SSIS, this code will help:

          SELECT SERVERPROPERTY ('MachineName') as MachineName,
SERVERPROPERTY ('InstanceName') as InstanceName,
SERVERPROPERTY ('ProductVersion') as ProductVersion,
SERVERPROPERTY ('ProductLevel') As ProductLevel,
SERVERPROPERTY ('Edition') as Edition,
SERVERPROPERTY ('LicenseType') as LicenseType,
SERVERPROPERTY ('NumLicenses') as NumLicenses,
SERVERPROPERTY ('IsClustered') as IsClustered,
SERVERPROPERTY ('IsIntegratedSecurityOnly') as IsIntegratedSecurityOnly

This weblog is sponsored by FITS.
We support PASS Germany!

I do not know the original author of this stored procedure...
If you are the one - please send me an email and I will publish the right credits!

After changing the structure of a table or delete one you will need to check and refresh your views also. This code will help...

          DECLARE @Table_Name varchar(100) DECLARE Refresh_Views CURSORFORSELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE
= 'VIEW'AND TABLE_SCHEMA = 'dbo'OPEN Refresh_Views FETCHNEXTFROM Refresh_Views INTO @Table_Name WHILE@@FETCH_STATUS =
0 BEGINEXEC sp_refreshview @Table_Name FETCHNEXTFROM Refresh_Views INTO @Table_Name ENDCLOSE Refresh_Views DEALLOCATE Refresh_Views

This weblog is sponsored by FITS.
We support PASS Germany!

Today at the BI Conference in Seattle Jeff Raikes from Microsoft has given a first official impact - just slides - on the next SQL Server version.

I have seen a new feature called Change Data Capture (also mentioned in the datasheet) in the session "PL208 Data Warehousing with Microsoft SQL Server 2005" presented by Gopal Ashok, Program Manager with the SQL Server Product team, which was quite impressive.

Ever dealt with the problem of having mass data in a table and no "real" marker to identify the specific rows for incremental data operations like inserts, updates and deletes?
The Change Data Capture should ease your pain by creating a kind of audit table to watch data modification without the need of programming stored procedures, triggers and so on.
I am extremely curious how this feature proves in real life scenarios. And I am also a little bit surprised if the SSIS team will not bring up some new tasks to enable the use of the audit table of the Change Data Capture - or do we still need to develop the handling concerning load cycles, deleting of successfully loaded records, etc by our own? A kind of automatic delta queue mechanism and handling like in SAP systems would be great!

Tomorrow Ted Kummert, corporate vice president of the Data and Storage Platform Division at Microsoft will have a keynote at the morning - I hope that he will show some more live impressions.

You will find the official press release here.

And there also is a information website online.

Regarding to the press release, "SQL Server “Katmai” is scheduled to be delivered in 2008" - but there is no information about CTP´s available on the website yet.

This weblog is sponsored by FITS.
We support PASS Germany!

Vera Noest provides a really great link collection of ressources which will be very usefull while doing troubleshoting.

Vera also provides troubleshooting tips for Terminal Server and Citrix.

Worth reading - check it out!

This weblog is sponsored by FITS.
We support PASS Germany!

Darren Gosbell has posted a very interesting article about querying SSAS and use the result in the relational part of SQL Server.

I have used it in a project to retransfer data which was calculated due to a parent child dimension containing items for a financial analysis.
Calculating this via the relational data would have been a mess, because of having to rebuild the whole calculation from the bottom to the top of the hierarchy.

All you have to do is to define a linked server in the first step:

          USE master GO /* Addnew linked
server */ EXEC sp_addlinkedserver @server='LINKED_OLAP', --
local SQL name given to the linked server  @srvproduct='', --
not used  @provider='MSOLAP.3', --
OLE DB provider (the .2 means the SQL2K version)  @datasrc='localhost', --
analysis server name (machine name)  @catalog='Adventure
Works DW'-- default catalog/database

After successfully defining the linked server you will be able to query any SSAS cube based on your security like this way:

          SELECT * FROMOpenQuery(linked_olap,'SELECT
--measures.members {Measures.[Internet Sales Amount]} ON COLUMNS, [Date].[Month].members
ON ROWS FROM [Adventure Works]') 

While writing this blog entry I tested it on my notebook also - and was very astonished that querying the cube does not work, while testing the linked server worked well.
I am getting an error message like:
"Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSOLAP.3" for linked server "linked_olap" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSOLAP.3" for linked server "linked_olap"."

Maybe it depends on Windows Vista, even with the security set in the right way and also running SSMS as an administrator...

This weblog is sponsored by FITS.
We support PASS Germany!

Narayana Vyas Kondreddi has published some nice little sql helpers to search and replace data in various tables - therefor the following content is intellectual property of Narayana Vyas Kondreddi.

Maybe you will have to adjust the sql commands to search only special tables via a prefix for example or to just search via a special column...

The first one, SearchAllTables, can search all the columns of all the tables in a given database for a specific keyword:

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT


CREATETABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SETNOCOUNTON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName ISNOTNULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECTMIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
ANDOBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName ISNOTNULL) AND (@ColumnName ISNOTNULL)
BEGIN
SET @ColumnName =
(
SELECTMIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
ANDQUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName ISNOTNULL
BEGIN
INSERTINTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END

The second one, SearchAndReplace, does not only perform the search but also will do a replace:

CREATE PROC SearchAndReplace
(
@SearchStr nvarchar(100),
@ReplaceStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string and replace it with another string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 2nd November 2002 13:50 GMT

SETNOCOUNTON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0

WHILE @TableName ISNOTNULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECTMIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
ANDOBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName ISNOTNULL) AND (@ColumnName ISNOTNULL)
BEGIN
SET @ColumnName =
(
SELECTMIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
ANDQUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName ISNOTNULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END

SELECT'Replaced ' + CAST(@RCTR ASvarchar) + ' occurence(s)'AS'Outcome'
END

This weblog is sponsored by FITS.
We support PASS Germany!

Interested in all the new things in SP2? Here is a complete list.

And this is the complete download site with links for the SP2 itself, a new BOL, new Samples, new Feature Pack and especially the links to SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies and SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007.

To avoid a restart after the installation of the SP2 you should stop the neccessary services which could be done with the following commands on the command line - if you have used the standard instances:

net stop MSSQLSERVER
net stop MSSQLServerOLAPService
net stop msftesql
net stop MsDtsServer
net stop ReportServer

After the installation the "SQL Server 2005 User Provisioning Tool is started automatically if you use Windows Vista. As you probably know, Windows Admins are not automatically also SQL or AS Admins  - this tool enables to set the right privileges to the administartor accounts.

But do not forget to start the services before you try to set the privileges...

If you want to add a new account you will have to start the Provisioning Tool again, but unluckily it is not added to the Start Menu, you will find it at:
C:\Program Files\Microsoft SQL Server\90\Shared\SqlProv.exe - assumed that you have a standard installation and an english version of MS Windows Vista.

This weblog is sponsored by FITS.
We support PASS Germany!

Sorry, German only...

PASS Deutschland e.V. bietet wieder eine interesssante Veranstaltung an und wir starten damit eine neue Reihe, die PASS Essentials. Ziel dieser Veranstaltung ist die Grundlagenvermittlung in einem spezifischen Thema des SQL Server 2005 innerhalb eines Tages mit einer begrenzten Anzahl an Teilnehmern und mit einen hohen Anteil Hands-On-Labs.

Los geht es mit dem "DBA StartUp", einem eintägigen Überblick über Installation,Backup & Restore, der am 23.November 2006 von 09.00 - 18.00 Uhr bei Microsoft in Bad Homburg stattfindet

Speaker ist mein hochgeschätzter PASS-Vorstandskollege Ralf Dietrich:
"Ralf Dietrich ist seit 1998 als Trainer und Consultant für Microsoft Betriebssysteme
und Datenbanken tätig. Als MCT, MCSE (Security) und MCITP für SQL Server
liegt sein Fokus in der Performanceanalyse und im Data Warehouse Design. Mit
Microsoft SQL Server 2005 beschäftigt er sich seit Mitte Juli 2003. Ihm liegen
sichere Servernetze & SQL-Server Installationen besonders am Herzen."

Hier der entsprechende Flyer PASSEssentials_DBAStartup.pdf (40.42 KB) mit mehr Informationen.

This weblog is sponsored by FITS.
We support PASS Germany!

Randy Dyess has published an article called "Auditing Your SQL Server Environment: Part II (Reviewing Role Memberships)"

The following content is intellectual property of Randy Dyess, the original posting can be found here:

"Roles should be in foremost in your mind when planning the security of your SQL Server environments. Auditing inherited SQL Server installations is a relatively easy thing to accomplish, and all DBAs should audit their environment and create documentation if they have not already done so. Once you can document the logins assigned to each of your fixed and user-defined roles, you can start to remove any duplication of permissions which can reduce the time needed to troubleshoot future permission errors."

--Use the master database
USE master
go

IFOBJECT_ID('dbo.spRoleMembers') ISNOTNULL
DROPPROCEDURE dbo.spRoleMembers
GO

CREATEPROCEDURE dbo.spRoleMembers
AS
/************************************************************
Creation Date: 04/28/02 Created By: Randy Dyess
Web Site: www.TransactSQL.Com
Email: RandyDyess@TransactSQL.Com
Purpose: Loops through all databases and obtains member
for database roles as well as server role members.
Location: master database
Output Parameters: None
Return Status: None
Called By: None
Calls: None
Data Modifications: None
Updates:
None

************************************************************/
SETNOCOUNTON

--Variables
DECLARE @lngCounter INTEGER
DECLARE @strDBName VARCHAR(50)
DECLARE @strSQL NVARCHAR(4000)

--Temp table to hold database and user-define role user names
CREATETABLE #tRolemember
(
strServerName VARCHAR(50) DEFAULT@@SERVERNAME
,strDBName VARCHAR(50)
,strRoleName VARCHAR(50)
,strUserName VARCHAR(50)
,strUserID VARCHAR(100)
)

--Temp table to hold database names
CREATETABLE #tDBNames
(lngID INTEGER IDENTITY(1,1)
,strDBName VARCHAR(50)
)

--Create permanent table
IFOBJECT_ID ('dbo.tRolemember') ISNULL
BEGIN
CREATETABLE dbo.tRolemember
(
strServerName VARCHAR(50)
,strDBName VARCHAR(50)
,strRoleName VARCHAR(50)
,strUserName VARCHAR(50)
,strUserID VARCHAR(100)
)
END

--Obtain members of each server role
INSERTINTO #tRolemember (strRoleName, strUserName, strUserID)
EXEC dbo.sp_helpsrvrolemember

--Obtain database names
INSERTINTO #tDBNames (strDBName)
SELECT name FROM master.dbo.sysdatabases
SET @lngCounter = @@ROWCOUNT

--Loop through databases to obtain members of database roles and user-defined roles
WHILE @lngCounter > 0
BEGIN

--Get database name from temp table
SET @strDBName = (SELECT strDBName FROM #tDBNames WHERE lngID = @lngCounter)

--Obtain members of each database and user-defined role
SET @strSQL = 'INSERT INTO #tRolemember (strRoleName, strUserName, strUserID)
EXEC ' + @strDBName + '.dbo.sp_helprolemember'

EXECsp_executesql @strSQL

--Update database name in temp table
UPDATE #tRolemember
SET strDBName = @strDBName
WHERE strDBName ISNULL

SET @lngCounter = @lngCounter - 1

END

--Place data into permanent table
INSERTINTO tRolemember
SELECT trm.* FROM #tRolemember trm
LEFTJOIN tRoleMember prm
ON trm.strUserName = prm.strUserName
AND trm.strDBName = prm.strDBName
AND trm.strRoleName = prm.strRoleName
AND trm.strServerName = prm.strServerName
WHERE prm.strServerName ISNULL

GO

--Test Stored Procedure
EXEC dbo.spRoleMembers

PRINT'Display by User'
SELECT strUserName, strDBName, strRoleName, strServerName FROM tRolemember
WHERE strUserName <> 'dbo'
ORDERBY strUserName

PRINT'Display by Role'
SELECT strRoleName, strDBName, strUserName,strServerName FROM tRolemember
WHERE strUserName <> 'dbo'
ORDERBY strRoleName

PRINT'Display by Database'
SELECT strDBName, strRoleName,strUserName, strServerName FROM tRolemember
WHERE strUserName <> 'dbo'
ORDERBY strDBName

Randy also has published a script about auditing user's passwords

The following content is intellectual property of Randy Dyess, the original posting can be found here:

IF OBJECT_ID('dbo.spAuditPasswords') ISNOTNULL
DROPPROCEDURE dbo.spAuditPasswords
GO

CREATEPROCEDURE dbo.spAuditPasswords
AS
/****************************************************************************
Creation Date: 03/22/02 Created By: Randy Dyess
Web Site: www.TransactSQL.Com
Email: RandyDyess@TransactSQL.Com
Purpose: Perform a simple audit of user's passwords
Location: master database
Output Parameters: None
Return Status: None
Called By: None
Calls: None
Data Modifications: None
Updates: None
Date Author Purpose
---------- -------------------------- ---------------------------------
****************************************************************************/
SETNOCOUNTON

--Variables
DECLARE @lngCounter INTEGER
DECLARE @lngCounter1 INTEGER
DECLARE @lngLogCount INTEGER
DECLARE @strName VARCHAR(256)

--Create table to hold SQL logins
CREATETABLE #tLogins
(
numID INTEGER IDENTITY(1,1)
,strLogin SYSNAMENULL
,lngPass INTEGER NULL
)

--Insert non ntuser into temp table
INSERTINTO #tLogins (strLogin)
SELECT name FROM master.dbo.sysloginsWHERE isntname = 0
SET @lngLogCount = @@ROWCOUNT

--Determine if password is null and user iis SQL Login
PRINT'The following logins have blank passwords'
SELECT name AS'Login Name'FROM master.dbo.syslogins
WHERE password ISNULL
AND isntname = 0


--Determine if password and name are the ssame
SET @lngCounter = @lngLogCount

WHILE @lngCounter <> 0
BEGIN
    SET @strName = (SELECT strLogin FROM #tLogins WHERE numID = @lngCounter)

    UPDATE #tLogins
    SET lngPass = (SELECT PWDCOMPARE (@strName,(SELECT password FROM master.dbo.sysloginsWHERE name = @strName)))
    WHERE numID = @lngCounter

    SET @lngCounter = @lngCounter - 1
END

PRINT'The following logins have passwords the same as their login name'
SELECT strLogin AS'Login Name'FROM #tLogins WHERE lngPass = 1

--Reset column for next password test
UPDATE #tLogins
SET lngPass = 0

--Determine if password is only one characcter long
SET @lngCounter = @lngLogCount

WHILE @lngCounter <> 0
BEGIN
    SET @lngCounter1 = 1
    SET @strName = (SELECT strLogin FROM #tLogins WHERE numID = @lngCounter)
    WHILE @lngCounter1 < 256
    BEGIN
        UPDATE #tLogins
        SET lngPass = (SELECT PWDCOMPARE (CHAR(@lngCounter1),(SELECT password FROM master.dbo.sysloginsWHERE name = @strName)))
        WHERE numID = @lngCounter
        AND lngPass <> 1
        
        SET @lngCounter1 = @lngCounter1 + 1
    END

    SET @lngCounter = @lngCounter - 1
END

PRINT'The following logins have one character passwords'
SELECT strLogin AS'Login Name'FROM #tLogins WHERE lngPass = 1
GO

--Test
EXEC dbo.spAuditPasswords

And last but not least Bradley Morris has published an article about "How to Script User and Role Object Permissions in SQL Server"

This weblog is sponsored by FITS.
We support PASS Germany!

The "Microsoft Architect Insight Conference" was held on 22 - 23 March 2006 and the slides are now available for download.

I would highly recommend that you read the PPT about Service-oriented Business Intelligence (SoBI) by Sean Gordon, Robert Grigg, Michael Horne, and Simon Thurman, which brings up some really good new approaches.
The authors also have published an article, which contains a more detailed descriptions and is available online.

And here are two additional links extracted from the article page:

• Data on the Outside vs. Data on the Inside
• Information as a Service: Service-Oriented Information Integration

You also should have an eye for the slides of Eric Nelson, who tries to explain that there are seven new servers in the SQL world...

This weblog is sponsored by FITS.
We support PASS Germany!

You surely know "Project REAL", the SQL Server 2005 reference implementation at Barnes & Noble. It contains information on all upcoming topics in a BI project.

And now...
your are not only able to read about it - you are able to download a whole subset of the project REAL data and the various parts of the implementation

Extract from the website:
"The kit contains:

1. A set of instructions for setting up the environment
2. Guidance on how to explore the implementation
3. A sample relational data warehouse database (a subset of the Project REAL data warehouse)
4. A sample source database (from which we pull incremental updates)
5. SSIS packages that implement the ETL operations
6. An SSAS cube definition and scripts for processing the cube from the sample warehouse
7. Sample SSRS reports
8. Sample data mining models for predicting out-of-stock conditions in stores
9. Sample client views in briefing books for the Proclarity and Panorama BI front-end tools

This kit will guide you through the key points to observe in the Project REAL implementation. Use it to learn and to get ideas for your own implementation. (While we believe it represents a very good design and generally follows best practices, it should not be regarded as the solution for every BI situation.) It will be helpful to see the overview presentations about the BI tools in SQL Server, or read documentation, before exploring the kit. One good source of information is to go through the tutorials that ship with SQL Server (the tutorials are installed when you select “Workstation components, Books Online and development tools” at installation time)."

This weblog is sponsored by FITS.
We support PASS Germany!

Chris Webb has blogged about the "Business Intelligence Architecture and Design Guide" downloadable at gotdotnet.

I agree with Chris, this is not really new stuff, but I personally think it is good to have informations like this in a kind of "complete package".

Extract from the website:
"The patterns & practices team is please to announce the availability of Architecture and Design Guidelines for Business Intelligence Applications. This exploratory community project provides prescriptive guidance on how to overcome architectural challenges and design issues when building Business Intelligence solutions using Microsoft platform. This guide is intended for software architects and developers who are developing Business Intelligence applications on the Microsoft .NET Framework using SQL Server 2005 - Integration Services and Analysis Services. Being an exploratory community project - anticipate gaps and scope for improvement. For more information see: http://codegallery.gotdotnet.com/biguide."

This weblog is sponsored by FITS.
We support PASS Germany!

If your users have used Excel 2000 and Pivot Table Services targeting SQL Server 2000 as data store, they probably will get the error message "User 'public' does not have permission to run DBCC TRACEON" - even if you have migrated the whole set of security settings.

Regarding to this link the only way would be to grant execute permissions on DBCC, because:
"[...] SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers) [...]"

In SQL Server 2000, non-Sys Admins could execute DBCC Traceon (208) - SQL Server 2005 prevents non-Sys Admins from executing DBCC's. Administrators will not get aware of this effect, because they are normally allowed to start a trace in SQL 2005....
So even IF you have tested the whole bunch of Excel files in the company you would not come across this error.

What does this mean for "real life"?
Just deleting your PTS-Excel files and building new ones?
Then I would suggest Reporting Services... ;-)
Screwing your security settings?
Not exactly....
:-)

As mentioned in the forum post, Excel sends a kind of identifier within its query string, which names the calling application and should look like this in the files you are having troubles with:
"APP=Microsoft® Query;"
But have you tried to change the query string in an Excel file afterwards? It is nearly impossible...

Luckily, you could also use this coding:

Sub PT_Connect_Change()

Dim sh As Worksheet, qy As QueryTable
Dim pt As PivotTable, pc As PivotCache
Dim OldPath AsString, NewPath AsString
Dim rng As Range

ForEach ws In ActiveWorkbook.Sheets
   ForEach pt In ws.PivotTables
      OldPath = pt.PivotCache.Connection
      NewPath =replace(OldPath, "APP=Microsoft® Query;", "")
      pt.PivotCache.Connection = Application.Substitute(pt.PivotCache.Connection, OldPath, NewPath)
      OnErrorResumeNext
      IfErr.Number <> 0 Then
         'you could write an entire log here...
         msgbox ("Error")
      EndIf
      pt.PivotCache.Refresh
   Next pt
Next ws

EndSub

This coding loops through all Pivot Table Services Connections in your open Excel Workbook and cuts of the string "APP=Microsoft® Query;" in the connection string and after that refreshes the connection and the data.

To be able to do this without evoking the above mentioned error message do NOT start refreshing of the queries at the beginning and try to avoid any actions with data extraction / connection. After that, start the VBA Editor, paste the coding and run the Sub.
Maybe you will have to wait some minutes because of the refreshing and depending on the ammount of the data and the numbers of connection strings, but this is neccessary - I encountered problems with the connection string change without doing it.

But you could spend the time in getting a coffee, posting a comment and reading this blog, for example...

This weblog is sponsored by FITS.
We support PASS Germany!

The SQL Server 2005 Service Pack 1 is available now, which updates your SQL Version to Build 9.00.2047.

• download english version
• download german version
• List of bugfixes
• List of new features

Microsoft also now has published the "SQL Server 2005 Express Edition with Advanced Services", which contains Reporting Services now, and the "Microsoft SQL Server 2005 Express Edition Toolkit", which contains the Business Intelligence Development Studio and a Software Development Kit!
SQL Server 2005 Express now really is worth a look and it is for free, you will be able to download it here:
http://msdn.microsoft.com/vstudio/express/sql/download/

This weblog is sponsored by FITS.
We support PASS Germany!

Sorry, German only...

Auf den Seiten des PASS Deutschland e.V. steht ein CommunityCast von Sebastian Weber zum Thema "SQL Server 2005 - CLR Entwicklung" zum Ansehen und zum Download zur Verfügung.

Am 02.03.2006 ab 19.00 Uhr wird es ebenfalls unter dieser Adresse eine Möglichkeit zum Chat mit Sebastian Weber geben, in dem entsprechende Fragen zum Thema CLR Entwicklung gestellt werden können.

Viel Spaß mit dem CommunityCast und herzlichen Dank an Sebastian Weber!

This weblog is sponsored by FITS.
We support PASS Germany!

Project REAL is a cooperative effort between Microsoft and a number of technology partners in the business intelligence (BI) industry to build on actual customer scenarios to discover best practices for creating BI applications based on SQL Server 2005. The term REAL in Project REAL is an acronym for Reference implementation, End-to-end, At scale, and Lots of users.

This page gives tons of informations and really is worth reading carefully and staying for hours!

This weblog is sponsored by FITS.
We support PASS Germany!

SQL Server 2000 Driver for JDBC
this support article maybe usefull for the first steps: Sample connection pool manager for use with Microsoft SQL Server 2000 Driver for JDBC

Microsoft SQL Server 2005 JDBC Driver

Ever thought about connecting your SAP system to your SQL Server?
Coming soon...

This weblog is sponsored by FITS.
We support PASS Germany!

Scott Lowe privides some interesting informations about pitfalls in his article

This tutorial shows you how to properly install Microsoft's SQL Server 2005, avoiding the pitfalls of an unsuccessful deployment.

This weblog is sponsored by FITS.
We support PASS Germany!

Have you also moaned about setting security for your stored proc´s?
I am shure, you have!
;-)

The following script should help you to set security - if you are using namespaces für your sproc´s....

if exists (select * from dbo.sysobjectswhere id =
object_id(N'[dbo].[spr_grantproc]') andOBJECTPROPERTY(id,
N'IsProcedure') = 1)
dropprocedure [dbo].[spr_grantproc]
GO

CREATEprocedure spr_grantproc
@login varchar(50),
@prefix varchar(50),
@grantbit
/* allocates the security settings to all SP´s beginning with the defined prefix (without itself)
@grant=1 grant access
@grant=0 revoke access
@login User-Login */

AS

declare @grantsql assysname
declare @action asvarchar(6)

if (@grant=1)
   set @action='GRANT'
else
   set @action='REVOKE'

if (@login isnotnull)
begin
   SELECT
      @Action+' EXECUTE ON ' + [NAME] + ' TO ' + @login as sel
   into
      #granttbl
   from
      SYSOBJECTS
   WHERE
      TYPE = 'P'
      ANDLEFT(NAME,LEN(@prefix)) = @prefix
      AND [NAME]<>'spr_grantproc'
   DECLARE Cur CURSORFOR
   SELECT sel FROM #granttbl
   OPEN Cur
   FETCH NEXT FROM Cur into @grantsql
      WHILE@@FETCH_STATUS = 0
         BEGIN
            exec(@grantsql)
            FETCH NEXT FROM Cur into @grantsql
         END
   CLOSE Cur
   DEALLOCATE Cur
   droptable #granttbl
end

GO

This weblog is sponsored by FITS.
We support PASS Germany!

Eric Bracke provides a cool tool named SQL Digger which enables searching in Stored Procedures, UDF´s, Views and Triggers.

He also provides guides to add it into SQL Enterprise Manager or in the .NET IDE.

This weblog is sponsored by FITS.
We support PASS Germany!

As Chris Webb posted here, there is a "SQL Server 2005 Business Intelligence Metadata Samples Toolkit", which brings up "Tools and Sample reports for Metadata Dependency Analysis of SSIS, SSAS", available for download for free.

Extract from the website:
"Microsoft SQL Server 2005 BI Metadata Samples provides tools and samples for analysing dependencies within and across SSIS and SSAS. The source code for the tools is provided.
The following are the components of this pack:
DependencyAnalyzer: a utility that scans SSIS packages and SSAS databases for enumerating metadata (objects, properties and dependencies).
DependencyViewer: a utility to view metadata organization & inter-dependencies (lineage/impact analysis).
Analysis Services Samples : sample SSAS packages that can be analyzed
Integration Services Samples: sample SSIS packages that can be analyzed
Report Model Samples (Report Builder): report builder model
Reports: sample reports built from metadata store
SQL: SQL script for creating metadata store"

Extract from the website:
"This white paper covers several interesting and unique methods for managing metadata in SQL Server Integration Services, Analysis Services and Reporting Services using built-in features including data lineage, business and technical metadata and impact analysis."

This weblog is sponsored by FITS.
We support PASS Germany!

This link provides the downloadable bits for the SQL Server Web Data Administrator and some hints.

Especially you will be interested in the following:
"Note: The SQL Web Data Administrator is not officially supported by Microsoft Product Support Services (PSS). If you require support for this tool, post your question in the SQL Web Data Administrator forum on the SQLJunkies site at http://www.sqljunkies.com/forums/.

The SQL Server Web Data Administrator enables you to easily manage your SQL Server data, wherever you are. Using its built-in features, you can do the following from Microsoft Internet Explorer or your favorite Web browser:

• Create and edit databases in SQL Server 2000 or Microsoft SQL Server 2000 Desktop Engine (MSDE 2000)
• Perform ad-hoc queries against databases and save them to your file system
• Export and import database schema and data
• Manage users and roles
• View, create and edit stored procedures

Whether you are doing Microsoft Windows or Web development, or just need remote access to data for yourself or your clients, the Web Data Administrator is the perfect complement to your toolbox."

This weblog is sponsored by FITS.
We support PASS Germany!