Phoenix

Database Documentation

noreply@blogger.com (Deepak) — Mon, 14 Mar 2011 11:42:51 PDT

Almost every application DBA would have a need to document the various database objects such as tables, views, stored procedures and functions. I wasn't an exception and exploring ways to document the 400+ tables we have. Basically, this would serve the following purpose:

1. Every one reading the document would have a fair idea as to where the table is being used and for what purpose a table was designed.
2. It makes the life easier for the experienced folks and improves productivity. As they no longer have to spend longer time in teaching the new joiners about the database schema.

Thanks to Microsoft, we have a few extended properties that make our life easier in documenting the database schema. I've created the below procedure based on few of those extended properties.

USE [MyDB]

GO

IF NOT EXISTS (SELECT name
FROM sys.objects
WHERE TYPE = 'P'
AND name = 'SetInfiObjectDescription')
BEGIN
EXEC('CREATE PROCEDURE Setinfiobjectdescription AS BEGIN SELECT 1 AS num END')
END

GO

ALTER PROCEDURE [dbo].[Setinfiobjectdescription] @ObjectName NVARCHAR(1000),
@ColumnName NVARCHAR(1000),
@ObjectDesc SQL_VARIANT
AS
BEGIN
IF @ColumnName IS NULL
BEGIN
IF NOT EXISTS(SELECT TOP 1 1
FROM sys.tables AS tbl
INNER JOIN sys.extended_properties AS p
ON p.major_id = tbl.object_id
AND p.class = 1
WHERE tbl.name = @ObjectName
AND p.name = 'TableDescription')
BEGIN
EXEC sys.Sp_addextendedproperty
@name = N'TableDescription',
@value = @ObjectDesc,
@level0type = N'SCHEMA',
@level0name = 'dbo',
@level1type = N'TABLE',
@level1name = @ObjectName
END
ELSE
BEGIN
EXEC sys.Sp_updateextendedproperty
@name=N'TableDescription',
@value= @ObjectDesc,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=@ObjectName
END
END
ELSE
BEGIN
IF NOT EXISTS(SELECT TOP 1 1
FROM sys.tables AS tbl
INNER JOIN sys.extended_properties AS p
ON p.major_id = tbl.object_id
AND p.class = 1
LEFT OUTER JOIN sys.all_columns AS clmns
ON clmns.object_id = tbl.object_id
AND p.minor_id = clmns.column_id
WHERE tbl.name = @ObjectName
AND p.name = 'ColumnDescription'
AND clmns.name = @ColumnName)
BEGIN
EXEC sys.Sp_addextendedproperty
@name = N'ColumnDescription',
@value = @ObjectDesc,
@level0type = N'SCHEMA',
@level0name = 'dbo',
@level1type = N'TABLE',
@level1name = @ObjectName,
@level2type = N'Column',
@level2name = @ColumnName
END
ELSE
BEGIN
EXEC sys.Sp_updateextendedproperty
@name=N'ColumnDescription',
@value= @ObjectDesc,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=@ObjectName,
@level2type = N'Column',
@level2name=@ColumnName
END
END
END

GO

The above procedure will add or modify the table or column description accordingly. The modifying as well as inserting logic is handled in the procedure, so just mention the description for the entity, it will overwrite or create the description accordingly.

Following are the steps to add table or column definition:

EXEC dbo.SetInfiObjectDescription

@ObjectName='TableName',

@Columnname=NULL,

@ObjectDesc=’Mention about the table and its usage’

EXEC dbo.SetInfiObjectDescription

@ObjectName='TableName',

@Columnname=’column name 1’,

@ObjectDesc=’Mention about the column 1 and its usage’

EXEC dbo.SetInfiObjectDescription

@ObjectName='TableName',

@Columnname=’column name 2’,

@ObjectDesc=’Mention about the column 2 and its usage’

The following query is used to fetch the table/column description:

SELECT tbl.name AS tablename,
clmns.name AS columnname,
p.name AS [Key],
p.VALUE AS DESCRIPTION
FROM sys.tables AS tbl
INNER JOIN sys.extended_properties AS p ON p.major_id = tbl.object_id AND p.class = 1 -- object/column
LEFT OUTER JOIN sys.all_columns AS clmns ON clmns.object_id = tbl.object_id AND p.minor_id = clmns.column_id

Tracking Sysadmin Rights

noreply@blogger.com (Deepak) — Fri, 24 Dec 2010 09:14:11 PST

I always wanted to track and get notified whenever a user is given the sysadmin privilege. By getting notified, I am aware of who are the sysadmin users accessing the system. I wrote a custom script, which realizes what I want to do. Whenever a person is given sysadmin privilege either through T-SQL queries or through SSMS, I get notified who was the grantor and who was the grantee.

Generally DBA's are the only sysadmins with some managers being given that privilege. If some unauthorized developer is made as sysadmin, I can track it and remove his permission before he/she screws up the database (may be accidentally).

I googled and couldn't find any available script. So, thought of writing this script as it will be of great help to a lot of DBA's. This actually helps me manage our SQL Servers more effectively.

IF NOT EXISTS(SELECT 1 FROM sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name='trg_adminrights')

BEGIN

EXEC('CREATE TRIGGER trg_adminrights ON ALL SERVER FOR ADD_SERVER_ROLE_MEMBER AS RETURN')

END

ALTER TRIGGER trg_adminrights

ON ALL SERVER

FOR ADD_SERVER_ROLE_MEMBER

BEGIN

DECLARE @username nvarchar(1000),

@subject nvarchar(1000),

@body nvarchar(max),

@rolename nvarchar(500),

@sql nvarchar(max),

@rolestartpos int,

@roleendpos int,

@loginname nvarchar(2000),

@len int

DECLARE @temp TABLE

(

commandtext nvarchar(max)

)

INSERT INTO @temp

SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

SELECT @sql=REPLACE(REPLACE(REPLACE(commandtext,'EXEC master..',''),'@loginame = N',''),'@rolename = N','')

FROM @temp

SET @sql=REPLACE(@sql,'''','') -- Replacing any string format

SET @sql=REPLACE(@sql,char(10),'') -- Replacing new line character

SET @sql=REPLACE(@sql,char(13),'') -- Replacing carriage return

SELECT @len = LEN(@sql)

SELECT @rolestartpos = (CHARINDEX(',',@sql) + 1)

SELECT @roleendpos = @len-@rolestartpos + 1

SELECT @rolename = SUBSTRING(@sql,@rolestartpos,@roleendpos)

SELECT @loginname = SUBSTRING(@sql,21,(CHARINDEX(',',@sql) - 21))

SET @username=SYSTEM_USER

SET @subject='SysAdmin rolemember added in ' + @@SERVERNAME

SET @body='SysAdmin privilege is given by: ' + @username + CHAR(13) + 'SysAdmin privilege is given to login: ' + @loginname + CHAR(13)

IF (LTRIM(RTRIM(@rolename)) = 'sysadmin') -- send email only if sysadmin role is given to a member

BEGIN

EXEC msdb..sp_send_dbmail

@profile_name='Infidba',

@recipients='XYZ@ABC.com',

@subject=@subject,

@body=@body

END

Fix Orphaned users - SQL 2008

noreply@blogger.com (Deepak) — Mon, 08 Nov 2010 19:16:26 PST

As a DBA, if you are managing 100 + databases it is always a nightmare to fix the orphaned users. Especially while migrating databases from one version to another through restore options we often encounter the scenario where there is a mismatch in SID (security identifiers). This happens only for SQL server logins as they are server specific, unlike the windows login which are domain specific.

I have written the below script which will fix all the orphaned users in all user databases. The below script is mainly for servers higher than SQL 2005 as it uses the new catalog views that are available only since SQL 2005. Moreover, the existing approach in SQL 2000 is to use sp_change_users_login procedure but Microsoft has mentioned that those procedures will soon be deprecated and are currently used only for backward compatibility. As a result once you have upgraded to SQL 2008 you can use the below script to fix the orphaned users.

USE master

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'FixOrphanedUsers')

BEGIN

EXEC('CREATE PROCEDURE [dbo].[FixOrphanedUsers] AS RETURN')

END

ALTER PROCEDURE dbo.FixOrphanedUsers

BEGIN

SET NOCOUNT ON

DECLARE @username varchar(200),@cmd nvarchar(1000),@dbname varchar(200),@sql varchar(2000)

DECLARE @orphanedusers TABLE

(

username sysname

)

DECLARE dbname CURSOR FOR

SELECT name FROM sys.databases WHERE name NOT IN ('master','model','msdb','tempdb') ORDER BY name ASC -- all except system databases

OPEN dbname

FETCH NEXT FROM dbname INTO @dbname

WHILE @@FETCH_STATUS=0

BEGIN

SET @sql='SELECT u.name FROM ' + QUOTENAME(@dbname) + '.sys.database_principals u

WHERE u.sid NOT IN (select sid FROM sys.server_principals P) AND type = ''S'' AND sid is not null

AND name NOT IN (''guest'',''dbo'')' -- only SQL logins that have sid

INSERT INTO @orphanedusers

EXEC(@sql)

DECLARE fixorphans CURSOR FOR

SELECT username FROM @orphanedusers

OPEN fixorphans

FETCH NEXT FROM fixorphans INTO @username

WHILE @@FETCH_STATUS = 0

BEGIN

IF EXISTS (SELECT name FROM sys.sql_logins WHERE name=@username) -- only SQL logins

BEGIN

SELECT @cmd = 'USE ' + @dbname + CHAR(13) + 'ALTER USER '+ @username + ' WITH LOGIN = ' + @username + CHAR(13)

EXEC sp_executesql @cmd

PRINT 'The orphaned Id''s for the user: ' +@username+ ' in the database '+@dbname+' is fixed successfully'

END

FETCH NEXT FROM fixorphans INTO @username

END

CLOSE fixorphans

DEALLOCATE fixorphans

DELETE FROM @orphanedusers

FETCH NEXT FROM dbname INTO @dbname

END

CLOSE dbname

DEALLOCATE dbname

SET NOCOUNT OFF

END