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
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
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
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
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
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...
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
To get all base tables of a view you can use the following CTE-Statement:
WITH CTE (VIEW_SCHEMA,VIEW_NAME,TABLE_SCHEMA,TABLE_NAME)
AS
(
SELECT VIEW_SCHEMA,VIEW_NAME,TABLE_SCHEMA,TABLE_NAME
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
)
SELECT vu.VIEW_SCHEMA,
vu.VIEW_NAME,
vu.TABLE_SCHEMA AS src_schema,
vu.TABLE_NAME AS src_table
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE VU LEFT JOIN CTE
ON VU.TABLE_SCHEMA =CTE.VIEW_SCHEMA
AND VU.TABLE_NAME=CTE.VIEW_name
If you need to fill up a string to a defined total count you will be glad if you have a function like this:
CREATE FUNCTION fn_filler(@txt varchar(max)='',@before bit=1,@fill char(1)='0',@len int)
RETURNS varchar(max)
AS
BEGIN
declare @anz as int
if @txt is null
set @txt=''
set @anz=@len-len(@txt)
if (@anz>0 )
if (@before=1)
set @txt=replace(space(@anz),' ',@fill)+@txt
else
set @txt=@txt+replace(space(@anz),' ',@fill)
return @txt
END
Do you need the last day of a month in a SQL Statement?
Here we go:
CREATE FUNCTION fn_LastDayOfMonth(@dat as datetime)
RETURNS smallint
AS
BEGIN
declare @ret as smallint
declare @mon as smallint
declare @dat2 as datetime
set @dat2=cast(year(@dat) as varchar(4))+right('0'+cast(month(@dat) as varchar(2)),2)+'01'
set @dat2=dateadd(month,1,@dat2)
set @dat2=DATEADD(day, -1,@dat2)
set @ret=day(@dat2)
return @ret
END
And if you need a complete date this would be an alternative:
CREATE FUNCTION [dbo].[fn_LastDayOfMonth](@dat as datetime)
RETURNS char(8)
AS
BEGIN
declare @ret as char(8)
declare @mon as smallint
declare @dat2 as datetime
set @dat2=cast(year(@dat) as varchar(4))+right('0'+cast(month(@dat) as varchar(2)),2)+'01'
set @dat2=dateadd(month,1,@dat2)
set @dat2=DATEADD(day, -1,@dat2)
set @ret=right('0'+cast(day(@dat2) as varchar(2)),2)+right('0'+cast(month(@dat) as varchar(2)),2)+cast(year(@dat) as varchar(4))
return @ret
END
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 every time a DELETE or UPDATE Modification will be executed.
The following content is intellectual property of Brett Kaiser, the original posting can be found here:
USE Northwind
SET NOCOUNT ON
GO
-- For example purposes, create a sample tables
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1), Col3 datetime DEFAULT(GetDate()), Col4 decimal(5,2))
CREATE TABLE x002548.myTable99(Col1 int IDENTITY(1,1), Col2 char(1), Col3 datetime DEFAULT(GetDate()), Col4 decimal(5,2))
GO
-- Give the sample tables some data
INSERT INTO myTable99(Col2, Col4)
SELECT 'x', 1 UNION ALL SELECT 'y', 2 UNION ALL SELECT 'z', 3
INSERT INTO x002548.myTable99(Col2, Col4)
SELECT 'a', 24 UNION ALL SELECT 'b', 25 UNION ALL SELECT 'c', 26
--Lets have a look
SELECT 'x002548' AS Source, * FROM x002548.myTable99
UNION ALL
SELECT 'dbo' AS Source, * FROM myTable99
GO
-- CREATE A Driver Table for all Tables in your catalog you wish to audit
CREATE TABLE myAudit99(TABLE_CATALOG sysname, TABLE_SCHEMA sysname, TABLE_NAME sysname)
GO
-- Populate the audit driver table with the table you want...you can use any type of process for this
INSERT INTO myAudit99(TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME LIKE 'myTable99%'
-- Lets create some audit tables based on the Driver
DECLARE myCursor99 CURSOR
FOR
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM myAudit99
DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAMES varchar(8000), @sql varchar(8000)
SELECT @COLUMN_NAMES = ''
OPEN myCursor99
FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'CREATE TABLE ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA + '.' + @TABLE_NAME + '_H ('
+ ' HIST_ADD_DT datetime DEFAULT (getDate()), HIST_ADD_TYPE char(1) NOT NULL'
+ ', HIST_ADD_SYSTEM_USER sysname NOT NULL, HIST_ADD_USER_NAME sysname NOT NULL'
+ ', HIST_ADD_HOSTNAME sysname NOT NULL, HIST_ADD_SPID int NOT NULL, HIST_ADD_DESC varchar(50) '
, @COLUMN_NAMES = @COLUMN_NAMES + ', ' + COLUMN_NAME
+ ' ' + DATA_TYPE
+ CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CONVERT(varchar(20),CHARACTER_MAXIMUM_LENGTH) + ')'
WHEN DATA_TYPE = 'decimal' THEN '(' + CONVERT(varchar(20),NUMERIC_PRECISION) + ','
+ CONVERT(varchar(20),NUMERIC_SCALE) + ')'
ELSE ''
END
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = @TABLE_CATALOG AND TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME
ORDER BY ORDINAL_POSITION
SELECT @SQL = @SQL + @COLUMN_NAMES + ')'
EXEC(@SQL)
SELECT @SQL = '', @COLUMN_NAMES = ''
FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
END
CLOSE myCursor99
DEALLOCATE myCursor99
GO
SELECT *
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME Like 'myTable99%'
ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
-- Now Lets create the audit TRIGGERS
DECLARE myCursor99 CURSOR
FOR
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM myAudit99
DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAMES varchar(8000), @sql varchar(8000)
SELECT @COLUMN_NAMES = ''
OPEN myCursor99
FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @COLUMN_NAMES = @COLUMN_NAMES + ', ' + COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = @TABLE_CATALOG AND TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME
ORDER BY ORDINAL_POSITION
SELECT @SQL = 'CREATE TRIGGER ' + @TABLE_SCHEMA + '_' + @TABLE_NAME + '_TR ON ' + @TABLE_SCHEMA + '.' +@TABLE_NAME
+ ' FOR UPDATE, DELETE AS '
+ ' DECLARE @HOSTNAME sysname, @DESC varchar(50) '
+ ' SELECT @HOSTNAME = hostname from master.dbo.sysprocesses where spid = @@SPID '
+ ' IF EXISTS(SELECT * FROM ' + @TABLE_NAME + ') SELECT @DESC = '
+ '''' + '''' + ' ELSE SELECT @DESC = ' + '''' + 'MASS DELETE' + ''''
+ ' If Exists (Select * From Inserted) And Exists (Select * From Deleted) '
+ ' INSERT INTO ' + @TABLE_NAME + '_H ( '
+ ' HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC'
+ @COLUMN_NAMES + ')'
+ ' SELECT ''U'', SYSTEM_USER, USER_NAME(), @HOSTNAME, @@SPID, @DESC'
+ @COLUMN_NAMES + ' FROM deleted'
+ ' If Not Exists (Select * From Inserted) And Exists (Select * From Deleted) And @DESC = '''' '
+ ' INSERT INTO ' + @TABLE_NAME + '_H ( '
+ ' HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC'
+ @COLUMN_NAMES + ')'
+ ' SELECT ''D'', SYSTEM_USER, USER_NAME(), @HOSTNAME, @@SPID, @DESC'
+ @COLUMN_NAMES + ' FROM deleted'
+ ' If Not Exists (Select * From Inserted) And Exists (Select * From Deleted) And @DESC <> '''' '
+ ' INSERT INTO ' + @TABLE_NAME + '_H ( '
+ ' HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC'
+ @COLUMN_NAMES + ')'
+ ' SELECT TOP 1 ''D'', SYSTEM_USER, USER_NAME(), @HOSTNAME, @@SPID, @DESC'
+ @COLUMN_NAMES + ' FROM deleted'
EXEC(@SQL)
SELECT @SQL = '', @COLUMN_NAMES = ''
FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
END
CLOSE myCursor99
DEALLOCATE myCursor99
GO
-- Now let's pretend to be an ignorant boss...not to much of a stretch there....
UPDATE myTable99 SET Col2 = 'G'
SELECT * FROM myTable99_H
DELETE FROM myTable99 WHERE Col1 = 1
SELECT * FROM myTable99_H
DELETE FROM x002548.myTable99
SELECT * FROM x002548.myTable99_H
SELECT * FROM myTable99
SELECT * FROM x002548.myTable99
GO
SET NOCOUNT OFF
DROP TABLE myAudit99
DROP TRIGGER dbo_myTable99_TR
DROP TRIGGER x002548_myTable99_TR
DROP TABLE dbo.myTable99
DROP TABLE x002548.myTable99
DROP TABLE dbo.myTable99_H
DROP TABLE x002548.myTable99_H
GO
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"
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.
I discovered a solution by Brendan Thomas "Create a SQL Server View of your AD Users" [2], but I got the same error mentioned in the comments.
The upcoming error could have different reasons:
- wrong registry entries regarding the ADSDSOObject
- syntax errors in the query
- the select statement contains the field "description" - seems that this field is not usable for a query
- missing user rights - the query is executed in the current user context if the linked server security properties are set to 'run in the actual security context'. If you aren´t using a AD-user the user will not be allowed to query the AD.
You can switch the settings to 'Be made without a security context' (as mentioned in [3]), but I have to test if the query will be executable without errors then.
After being able to execute the query without errors I searched for other fields that could be extracted from the AD - [6] contains a list.
If anyone has a kind of official listing - please post it in the comments - I was not able to find one using Google...
The extended version of Brendan´s script with the additional fields I needed:
first step:
add the linked server
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
second step:
execute query
select
title,
sAMAccountName,
givenname as 'First name',
middleName as Initials,
sn as 'Last name',
displayName,
telephonenumber as Telephone,
mail as Mail,
info as Remarks,
title as Title,
department as Department,
company as Company,
manager as Manager
from
openquery
(
ADSI,
'SELECT
title,
sAMAccountName,
givenname,
middleName,
sn,
displayName,
telephonenumber,
mail,
info,
title,
department,
company,
manager
FROM
''LDAP://YourLDAPServer''
WHERE
objectCategory = ''Person''
AND objectClass = ''user''
')
third step:
drop server (if neccessary)
sp_dropserver 'ADSI', 'droplogins'
Searching for the mentioned error code I discovered a query call [5] which could be executed without using the linked server, using OPENROWSET instead of OPENQUERY, but you would have to submit User and Password if you would like to use another user context with the appropiate rights - and this user and his password would be included plain the query...
select
*
from
OPENROWSET
(
'AdsDsoObject',
'User ID=;Password=;ADSI Flag=0x11;Page Size=10000',
'SELECT
sAMAccountName,
givenname,
middleName,
sn,
displayName
FROM
''LDAP://YourLDAPServer''
WHERE
objectCategory = ''Person''
AND objectClass = ''user''
')
If you have a large Active Directory you will come across the problem, that calling the AD will only bring up the first thousand users by default using the OPENQUERY (compare [7]).
The parameter Page Size used in the query with the OPENROWSET would override this default value.
Depending on this default, the usage and performance issues I would prefer the data being stored in the SQL Server database - the MS Scripting Guy has a good hint on how to do this:
On Error Resume Next
Const ADS_SCOPE_SUBTREE = 2
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.CommandText = _
"SELECT Name FROM 'LDAP://dc=fabrikam,dc=com' " & _
"WHERE objectCategory='user'"
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
Wscript.Echo objRecordSet.Fields("Name").Value
objRecordSet.MoveNext
Loop
Links:
- http://support.microsoft.com/default.aspx?scid=kb;en-us;Q299410
- http://codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx
- http://www.dbforums.com/archive/index.php/t-958399.html
- http://www.codeproject.com/aspnet/LikeDataStores.asp
- http://www.mcse.ms/archive81-2005-4-1260672.html
- http://forums.aspfree.com/asp-development-5/asp-script-for-querying-active-directory-user-details-using-adsi-21470.html
- http://www.microsoft.com/technet/scriptcenter/resources/qanda/aug04/hey0824.mspx
If you have any enhancements (for example further fields - I am searching for the domain...) feel free to use the comments.
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!
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
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!
But be carefull, this sproc is using dynamic statements via exec '' and therefor alos including all upcoming issues...
Not known? Than you have to read the article "The Curse and Blessings of Dynamic SQL" from Erland Sommarskog.
Here we go:
CREATE PROCEDURE spr_GetSortedPage(
@TableName VARCHAR(50),
@PrimaryKey VARCHAR(25),
@SortField VARCHAR(100),
@PageSize INT,
@PageIndex INT = 1,
@QueryFilter VARCHAR(100) = NULL
) AS
SET NOCOUNT ON
DECLARE @SizeString AS VARCHAR(5)
DECLARE @PrevString AS VARCHAR(5)
SET @SizeString = CONVERT(VARCHAR, @PageSize) SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex - 1))
IF @QueryFilter IS NULL OR @QueryFilter = ''
BEGIN
EXEC(
'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
(SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' NOT IN
(SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField
)
EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName)
END
ELSE
BEGIN
EXEC(
'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
(SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' AND ' + @PrimaryKey + ' NOT IN
(SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField
)
EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName + ' WHERE ' + @QueryFilter)
END
RETURN 0
GO
Doing it this way brings up to resultsets in one call - because of classic ASP having trouble with that you have to exclude the second exec´s bringing up the page-information and put them in seperate sproc´s.
|