some thoughts... - Development|T-SQL

little SQL-helpers: split a column

Markus Fischer — Mon, 24 Sep 2007 18:38:48 GMT

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 ( position int IDENTITY PRIMARY KEY, value nvarchar(max)
) AS BEGIN DECLARE @index int SET @index =
-1 WHILE (LEN(@text) > 0) BEGIN SET @index =
CHARINDEX(@delimiter , @text) IF (@index =
0) AND (LEN(@text) > 0) BEGIN INSERT INTO @Strings VALUES (@text) BREAK END IF (@index >
1) BEGIN INSERT INTO @Strings VALUES (LEFT(@text,
@index - 1)) SET @text = RIGHT(@text,
(LEN(@text) - @index)) END ELSE SET @text
= RIGHT(@text, (LEN(@text) - @index)) END RETURN END

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

little SQL-helpers: help with date formats

Markus Fischer — Mon, 24 Sep 2007 16:33:12 GMT

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)) RETURNS varchar(255) AS BEGIN declare @ret varchar(255) declare @i int --
year 4 digits set @ret=replace(@format,'\jjjj',cast(year(@dat) as varchar(4))) --
year 4 digits  set @ret=replace(@ret,'\yyyy',cast(year(@dat) as varchar(4))) --
day 2 digits  set @ret=replace(@ret,'\dd',right('0'+cast(day(@dat) as varchar(2)),2)) --
weekday 1 digit  set @ret=replace(@ret,'\dw',cast(datepart(dw,@dat) as varchar(1))) --
day of year set @ret=replace(@ret,'\dy',right('00'+cast(day(@dat) as varchar(3)),3)) --
hour 2 digits set @ret=replace(@ret,'\hh',right('0'+cast(datepart(hh,@dat) as varchar(2)),2)) --
year 2 digits  set @ret=replace(@ret,'\jj',right(cast(year(@dat) as varchar(4)),2)) --
minute 2 digits  set @ret=replace(@ret,'\mi',right('0'+cast(datepart(mi,@dat) as varchar(2)),2)) --
month 2 digits set @ret=replace(@ret,'\mm',right('0'+cast(month(@dat) as varchar(2)),2)) --
milliseconds 3 digits set @ret=replace(@ret,'\ms',right('00'+cast(datepart(ss,@dat) as varchar(3)),3)) --
quarter 2 digits  set @ret=replace(@ret,'\qq',right('0'+cast(datepart(qq,@dat) as varchar(2)),2)) --
seconds 2 digits set @ret=replace(@ret,'\ss',right('0'+cast(datepart(ss,@dat) as varchar(2)),2)) --
day 2 digits set @ret=replace(@ret,'\tt',right('0'+cast(day(@dat) as varchar(2)),2)) --
week 2 digits set @ret=replace(@ret,'\wk',right('0'+cast(datepart(wk,@dat) as varchar(2)),2)) --
year 2 digits set @ret=replace(@ret,'\yy',right(cast(year(@dat) as varchar(4)),2)) --
day 1-2 digits  set @ret=replace(@ret,'\d',cast(day(@dat) as varchar(2))) --
hour 1-2 digits  set @ret=replace(@ret,'\h',cast(datepart(hh,@dat) as varchar(2))) --
month 1-2 digits  set @ret=replace(@ret,'\m',cast(month(@dat) as varchar(2))) --
minute 2 digits set @ret=replace(@ret,'\n',right('0'+cast(datepart(mi,@dat) as varchar(2)),2)) --
quarter 1 digit  set @ret=replace(@ret,'\q',cast(datepart(qq,@dat) as varchar(1))) --
seconds 1-2 digits set @ret=replace(@ret,'\s',cast(datepart(ss,@dat) as varchar(2))) --
day 1-2 digits  set @ret=replace(@ret,'\t',cast(day(@dat) as varchar(2))) --
week 1-2 digits  set @ret=replace(@ret,'\w',cast(datepart(wk,@dat) as varchar(2))) return @ret END GO

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

little SQL-helpers: Create database snapshots via stored procedure

Markus Fischer — Mon, 24 Sep 2007 16:14:49 GMT

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),
@Execute BIT = 1) AS SET NOCOUNT ON DECLARE @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 INT DECLARE @indexPfad INT DECLARE @lenFname INT DECLARE @lenPfad INT DECLARE @lenDB INT DECLARE @lenExt INT CREATE TABLE #Info
(physical_name VARCHAR(255) not null,
logicname VARCHAR(255) not null) 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 FOR SELECT physical_name,
logicname FROM #info OPEN c FETCH NEXT FROM c INTO @fname,@LogicName WHILE (@@fetch_status
<> -1) BEGIN IF (@@fetch_status
<> -2) BEGIN SET @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 END FETCH NEXT FROM c INTO @fname,@LogicName END CLOSE c DEALLOCATE c SET @Command
= LEFT(@Command,LEN(@Command)-3) + @NewLine + 'AS
SNAPSHOT OF ' + @masterdb IF @Execute =
1 BEGIN EXEC (@Command) END ELSE BEGIN SELECT @Command AS Command END

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

little SQL-helpers: Get some infos about your SQL Server

Markus Fischer — Mon, 24 Sep 2007 15:57:03 GMT

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!

little SQL-helpers: sp_refreshview

Markus Fischer — Mon, 24 Sep 2007 15:51:39 GMT

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 CURSOR FOR SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE
= 'VIEW' AND TABLE_SCHEMA = 'dbo' OPEN Refresh_Views FETCH NEXT FROM Refresh_Views INTO @Table_Name WHILE @@FETCH_STATUS =
0 BEGIN EXEC sp_refreshview @Table_Name FETCH NEXT FROM Refresh_Views INTO @Table_Name END CLOSE Refresh_Views DEALLOCATE Refresh_Views

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

little SQL-helpers: Extracting Olap Data from SSAS using SSIS - Darren Gosbell

Markus Fischer — Tue, 01 May 2007 22:05:16 GMT

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 /* Add new 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 * FROM OpenQuery(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!

little SQL-helpers: search and replace data from Narayana Vyas Kondreddi

Markus Fischer — Tue, 01 May 2007 19:37:56 GMT

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

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

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

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(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')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #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

SET NOCOUNT ON

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 IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(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')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
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 AS varchar) + ' occurence(s)' AS 'Outcome'
END

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

little SQL-helpers: all base tables of a view

Klaus Hoeltgen — Tue, 24 Apr 2007 12:20:16 GMT

To get all base tables of a view you can use the following CTE-Statement...

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

little SQL-helpers: fillup string

Markus Fischer — Wed, 31 Jan 2007 09:51:35 GMT

If you need to fill up a string to a defined total count you will be glad if you have a function like this:

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

little SQL-helpers: Last day of month

Markus Fischer — Wed, 31 Jan 2007 09:46:00 GMT

Do you need the last day of a month in a SQL Statement?

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

System Views in SQL Server 2005

Markus Fischer — Wed, 25 Oct 2006 22:00:30 GMT

You will find a complete overview for the System Views at http://msdn2.microsoft.com/en-us/library/ms177862.aspx. The most usefull ones of the Catalog Views probably will be Object Catalog Views Security Catalog Views To query metadata you will have to use the Information Schema Views. If you need some hints on how to use the SQL Server System Catalog have a look at Querying the SQL Server System Catalog and the Querying the SQL Server System Catalog FAQ for some examples.

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

Brett Kaiser: How do I track data changes in a database

Markus Fischer — Sun, 27 Aug 2006 14:09:14 GMT

Brett Kaiser has posted a article called "How do I track data changes in a database" including his real usefull coding about his idea of doing this via T-SQL automatically erver time a DELETE or UPDATE Modification will be done. The following content is intellectual property of Brett Kaiser, the original posting can be found here:

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

Common table expression (CTE) in SQL Server 2005

Markus Fischer — Sun, 27 Aug 2006 13:39:40 GMT

Nigel Rivett has published an interesting detailed article about common table expressions in SQL Server 2005 on simple-talk.com.

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

some SQL Server scripts regarding security

Markus Fischer — Fri, 25 Aug 2006 13:19:02 GMT

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

IF OBJECT_ID('dbo.spRoleMembers') IS NOT NULL
DROP PROCEDURE dbo.spRoleMembers
GO

CREATE PROCEDURE 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

************************************************************/
SET NOCOUNT ON

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

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

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

--Create permanent table
IF OBJECT_ID ('dbo.tRolemember') IS NULL
BEGIN
CREATE TABLE dbo.tRolemember
(
strServerName VARCHAR(50)
,strDBName VARCHAR(50)
,strRoleName VARCHAR(50)
,strUserName VARCHAR(50)
,strUserID VARCHAR(100)
)
END

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

--Obtain database names
INSERT INTO #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'

EXEC sp_executesql @strSQL

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

SET @lngCounter = @lngCounter - 1

END

--Place data into permanent table
INSERT INTO tRolemember
SELECT trm.* FROM #tRolemember trm
LEFT JOIN 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 IS NULL

GO

--Test Stored Procedure
EXEC dbo.spRoleMembers

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

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

PRINT 'Display by Database'
SELECT strDBName, strRoleName,strUserName, strServerName FROM tRolemember
WHERE strUserName <> 'dbo'
ORDER BY 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') IS NOT NULL
DROP PROCEDURE dbo.spAuditPasswords
GO

CREATE PROCEDURE 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
---------- -------------------------- ---------------------------------
****************************************************************************/
SET NOCOUNT ON

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

--Create table to hold SQL logins
CREATE TABLE #tLogins
(
numID INTEGER IDENTITY(1,1)
,strLogin SYSNAME NULL
,lngPass INTEGER NULL
)

--Insert non ntuser into temp table
INSERT INTO #tLogins (strLogin)
SELECT name FROM master.dbo.syslogins WHERE 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 IS NULL
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.syslogins WHERE 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.syslogins WHERE 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!

SQL Server View of AD Users or Performing a SQL Distributed Query by Using ADSI

Markus Fischer — Mon, 06 Mar 2006 04:55:58 GMT

Being annoyed about the Reporting Services Execution Log just bringing up the domain users and having a customer without speaking usernames I searched for a solution to join Execution Log and the Active Directory to enrich the usernames to be able to identify the users.

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

CommunityCast: SQL Server 2005 - CLR Entwicklung

Markus Fischer — Thu, 23 Feb 2006 11:03:12 GMT

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!

Stored Procedure for granting authorization

Markus Fischer — Wed, 04 Jan 2006 04:36:13 GMT

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.sysobjects where id =
object_id(N'[dbo].[spr_grantproc]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[spr_grantproc]
GO

CREATE procedure spr_grantproc
@login varchar(50),
@prefix varchar(50),
@grant bit
/* 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 as sysname
declare @action as varchar(6)

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

if (@login is not null)
begin
   SELECT
      @Action+' EXECUTE ON ' + [NAME] + ' TO ' + @login as sel
   into
      #granttbl
   from
      SYSOBJECTS
   WHERE
      TYPE = 'P'
      AND LEFT(NAME,LEN(@prefix)) = @prefix
      AND [NAME]<>'spr_grantproc'
   DECLARE Cur CURSOR FOR
   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
   drop table #granttbl
end

GO

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

Generic Paging solved with a Stored Procedure

Markus Fischer — Wed, 04 Jan 2006 02:22:20 GMT

I found the following code somewhere in the internet sometimes ago, but did not store the link, if someone wants to be honoreed for creation - please mail to me!

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