How-to save object permissions for a database during development

-- HOW-TO MOVE ALL THE PRODUCTION'S DATABASE PERMISSIONS ON A DEV DB

-- and ensure that each time you change the DDL in your db  the same 

-- permissions will apply 

-- The script for getting the permissions is from  here 

USE [DB_DEV]

GO

/****** Object:  Table [dbo].[Permissions]    Script Date: 02/22/2010 12:13:30 ******/

IF  EXISTS (SELECT * FROM sys.objects 

WHERE object_id = OBJECT_ID(N'[dbo].[Permissions]') AND type in (N'U'))

DROP TABLE [dbo].[Permissions]

GO

USE [DB_DEV]

GO

/****** Object:  Table [dbo].[Permissions]    Script Date: 02/22/2010 12:13:31 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Permissions](

[PermissionsId] [int] IDENTITY(1,1) NOT NULL,

[principle_name] [nvarchar](20) NULL,

[principle_type_desc] [nvarchar](20) NULL,

[object_name] [nvarchar](100) NULL,

[permission_name] [nvarchar](100) NULL,

[permission_state_desc] [nvarchar](100) NULL,

CONSTRAINT [PK_Permissions] PRIMARY KEY CLUSTERED 

(

[PermissionsId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 

ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

-- Now simply copy the permissions from the production's 

-- fullback database to the new Permissions table 

use DB_FB

GO

INSERT INTO [DB_TEST].[dbo].[Permissions]

([principle_name]

,[principle_type_desc]

,[object_name]

,[permission_name]

,[permission_state_desc])

select dp.NAME AS principal_name,

dp.type_desc AS principal_type_desc,

o.NAME AS object_name,

p.permission_name,

p.state_desc AS permission_state_desc 

from    sys.database_permissions p

left    OUTER JOIN sys.all_objects o

on     p.major_id = o.OBJECT_ID

inner   JOIN sys.database_principals dp

on     p.grantee_principal_id = dp.principal_id

IF  EXISTS (SELECT * FROM sys.triggers 

WHERE parent_class_desc = 'DATABASE' AND name = N'trig_ApplyPermissions')

DISABLE TRIGGER [trig_ApplyPermissions] ON DATABASE

GO

USE [DB_DEV]

GO

/****** Object:  DdlTrigger [trig_ApplyPermissions]    Script Date: 02/22/2010 11:35:36 ******/

IF  EXISTS (SELECT * FROM sys.triggers 

WHERE parent_class_desc = 'DATABASE' AND name = N'trig_ApplyPermissions')DROP TRIGGER [trig_ApplyPermissions] ON DATABASE

GO

USE [DB_DEV]

GO

/****** Object:  DdlTrigger [trig_ApplyPermissions]    Script Date: 02/22/2010 11:35:36 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create trigger [trig_ApplyPermissions]

on database

for create_procedure, alter_procedure, 

create_table, alter_table, 

create_function, alter_function

as

set nocount on

declare @data xml

set @data = EVENTDATA()

declare @objName nvarchar(200) 

set @objname  = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')

declare @sqlCode nvarchar(max) 

set @sqlCode = '' 

--Grab all the functions for the current DB 

SELECT 

IDENTITY(INT,1,1) AS PermissionsId,

[principle_name]

,[principle_type_desc]

,[object_name]

,[permission_name]

,[permission_state_desc]

INTO #ObjList

FROM [Permissions]

where [object_name] = @objName

DECLARE

@Loopid INT,

@MaxId INT,

@principle_name nvarchar(100) , 

@principle_type_desc nvarchar(100) , 

@object_name nvarchar(100) , 

@permission_name nvarchar(100), 

@permission_state_desc nvarchar(100)

--Grab start and end values for the loop

SELECT @Loopid = 1,

@MaxId = MAX(PermissionsId)

FROM #ObjList

--This is where the loop starts

WHILE @Loopid <= @MaxId BEGIN

--grab the function name and type

SELECT 

@principle_name = [principle_name] , 

@principle_type_desc   = [principle_type_desc] , 

@object_name = [object_name] , 

@permission_name = [permission_name] , 

@permission_state_desc = [permission_state_desc]

FROM #ObjList

WHERE PermissionsId = @Loopid

SELECT @sqlCode = 

@permission_state_desc + ' ' + @permission_name + 

' ON ' + @object_name+ 

' TO ' + @principle_name + ' '  + 

' as DBO ;' FROM [Permissions] 

EXEC sp_executesql @sqlCode 

--increment counter

SET @Loopid = @Loopid + 1

END --eof while 

--clean up

DROP TABLE #ObjList

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

DISABLE TRIGGER [trig_ApplyPermissions] ON DATABASE

GO

ENABLE TRIGGER [trig_ApplyPermissions] ON DATABASE

GO

how-to get the list of a columns in tsql

Courtesy to :
James Horsley
http://www.sqlservercentral.com/Forums/Topic865846-2623-3.aspx#bm866809

declare @tableName varchar(200) = 'TableToGetTheColsFor'

declare @cols varchar(max)

SET @Cols = ''

SELECT 

@Cols = @Cols + CASE WHEN ORDINAL_POSITION = 1 THEN '' ELSE ',' END + COLUMN_NAME 

FROM 

INFORMATION_SCHEMA.COLUMNS 

WHERE 

TABLE_NAME=@tableName

SELECT @cols  

.Net to C# type converter

using System;

namespace Gaf.Utils

{

/// <summary>

/// This class converts the .Net reflected Built-In Types ( passed as strings to different string codes ) 

/// </summary>

class Converter

{

/// <summary>

/// Converts the reflected .Net type passed as string to a C# built-in type as string

/// </summary>

/// <param name="strDotNetTypeString">The .Net type passed as string</param>

/// <returns>the converted built-in C# type </returns>

public static string FromDotNetTypeToCSharpType( string strDotNetTypeString ) 

{

string strConvertTo = strDotNetTypeString;

#region DefineTheConvertedType

//<source>http://msdn.microsoft.com/en-us/library/ya5y69ds.aspx</source>

//<source>http://ysgitdiary.blogspot.com/2010/02/c-types-vs-net-types-code-generation.html</source>

switch (strDotNetTypeString)

{

case "System.Boolean":

strConvertTo = "bool";

break;

case "System.Byte":

strConvertTo = "byte";

break;

case "System.SByte":

strConvertTo = "sbyte";

break;

case "System.Char":

strConvertTo = "char";

break;

case "System.Decimal":

strConvertTo = "decimal";

break;

case "System.Double":

strConvertTo = "double";

break;

case "System.Single":

strConvertTo = "float";

break;

case "System.Int32":

strConvertTo = "int";

break;

case "System.UInt32":

strConvertTo = "uint";

break;

case "System.Int64":

strConvertTo = "long";

break;

case "System.UInt64":

strConvertTo = "ulong";

break;

case "System.Object":

strConvertTo = "object";

break;

case "System.Int16":

strConvertTo = "short";

break;

case "System.UInt16":

strConvertTo = "ushort";

break;

case "System.String":

strConvertTo = "string";

break;

case "System.Nullable`1[System.Boolean]":

strConvertTo = "bool?";

break;

case "System.Nullable`1[System.Byte]":

strConvertTo = "byte?";

break;

case "System.Nullable`1[System.SByte]":

strConvertTo = "sbyte?";

break;

case "System.Nullable`1[System.Char]":

strConvertTo = "char?";

break;

case "System.Nullable`1[System.Decimal]":

strConvertTo = "decimal?";

break;

case "System.Nullable`1[System.Double]":

strConvertTo = "double?";

break;

case "System.Nullable`1[System.Single]":

strConvertTo = "float?";

break;

case "System.Nullable`1[System.Int32]":

strConvertTo = "int?";

break;

case "System.Nullable`1[System.UInt32]":

strConvertTo = "uint?";

break;

case "System.Nullable`1[System.Int64]":

strConvertTo = "long?";

break;

case "System.Nullable`1[System.UInt64]":

strConvertTo = "ulong?";

break;

case "System.Nullable`1[System.Object]":

strConvertTo = "object?";

break;

case "System.Nullable`1[System.Int16]":

strConvertTo = "short?";

break;

case "System.Nullable`1[System.UInt16]":

strConvertTo = "ushort?";

break;

case "System.Nullable`1[System.String]":

strConvertTo = "string?";

break;

}//eof switch 

return strConvertTo;

#endregion DefineTheConvertedType

} //eof method 

/// <summary>

/// Convertes a .Net reflected Build-In type passed as string to a string code to convert this type in 

/// to basic Convert.ToTypeOfType sting code

/// </summary>

/// <param name="strDotNetTypeString">the basic built-in type as string </param>

/// <returns>the basic built-in type </returns>

public static string FromDotNetTypeToConvertToDotNetTypeCode(string strDotNetTypeString)

{

string strConvertTo = strDotNetTypeString;

#region DefineTheConvertedType

//<source>http://msdn.microsoft.com/en-us/library/ya5y69ds.aspx</source>

//<source>http://ysgitdiary.blogspot.com/2010/02/c-types-vs-net-types-code-generation.html</source>

switch (strDotNetTypeString)

{

case "System.Boolean":

strConvertTo = "Convert.ToBoolean";

break;

case "System.Byte":

strConvertTo = "Convert.ToByte";

break;

case "System.SByte":

strConvertTo = "Convert.ToSByte";

break;

case "System.Char":

strConvertTo = "Convert.ToChar";

break;

case "System.Decimal":

strConvertTo = "Convert.ToDecimal";

break;

case "System.Double":

strConvertTo = "Convert.ToDouble";

break;

case "System.Single":

strConvertTo = "Convert.ToSingle";

break;

case "System.Int32":

strConvertTo = "Convert.ToInt32";

break;

case "System.UInt32":

strConvertTo = "Convert.ToUInt32";

break;

case "System.Int64":

strConvertTo = "Convert.ToInt64";

break;

case "System.UInt64":

strConvertTo = "Convert.ToUInt64";

break;

case "System.Object":

strConvertTo = "Convert.ToObject";

break;

case "System.Int16":

strConvertTo = "Convert.ToInt16";

break;

case "System.UInt16":

strConvertTo = "Convert.ToUint16";

break;

case "System.String":

strConvertTo = "Convert.ToString";

break;

case "System.Nullable`1[System.Boolean]":

strConvertTo = "Convert.ToBool";

break;

case "System.Nullable`1[System.Byte]":

strConvertTo = "Convert.ToByte";

break;

case "System.Nullable`1[System.SByte]":

strConvertTo = "Convert.ToSByte";

break;

case "System.Nullable`1[System.Char]":

strConvertTo = "Convert.ToChar";

break;

case "System.Nullable`1[System.Decimal]":

strConvertTo = "Convert.ToDecimal";

break;

case "System.Nullable`1[System.Double]":

strConvertTo = "Convert.ToDouble";

break;

case "System.Nullable`1[System.Single]":

strConvertTo = "Convert.ToSingle";

break;

case "System.Nullable`1[System.Int32]":

strConvertTo = "Convert.ToInt32";

break;

case "System.Nullable`1[System.UInt32]":

strConvertTo = "Convert.ToUInt32";

break;

case "System.Nullable`1[System.Int64]":

strConvertTo = "Convert.ToInt64";

break;

case "System.Nullable`1[System.UInt64]":

strConvertTo = "Convert.ToUInt64";

break;

case "System.Nullable`1[System.Object]":

strConvertTo = "Convert.ToObject";

break;

case "System.Nullable`1[System.Int16]":

strConvertTo = "Convert.ToInt16";

break;

case "System.Nullable`1[System.UInt16]":

strConvertTo = "Convert.ToUInt16";

break;

case "System.Nullable`1[System.String]":

strConvertTo = "Convert.ToString";

break; 

}//eof switch 

return strConvertTo;

#endregion DefineTheConvertedType

} //eof method 

} //eof class 

} //eof 

Converter class derived from previous post ...

using System;

namespace Gaf.Utils

{

class Converter

{

public static string FromDotNetTypeToCSharpType( string strDotNetTypeString ) 

{

string strConvertedType = strDotNetTypeString; 

#region DefineTheConvertedType

//<source>http://msdn.microsoft.com/en-us/library/ya5y69ds.aspx</source>

//<source>http://ysgitdiary.blogspot.com/2010/02/c-types-vs-net-types-code-generation.html</source>

switch (strDotNetTypeString)

{

case "System.Boolean":

strConvertedType = "bool";

break;

case "System.Byte":

strConvertedType = "byte";

break;

case "System.SByte":

strConvertedType = "sbyte";

break;

case "System.Char":

strConvertedType = "char";

break;

case "System.Decimal":

strConvertedType = "decimal";

break;

case "System.Double":

strConvertedType = "double";

break;

case "System.Single":

strConvertedType = "float";

break;

case "System.Int32":

strConvertedType = "int";

break;

case "System.UInt32":

strConvertedType = "uint";

break;

case "System.Int64":

strConvertedType = "long";

break;

case "System.UInt64":

strConvertedType = "ulong";

break;

case "System.Object":

strConvertedType = "object";

break;

case "System.Int16":

strConvertedType = "short";

break;

case "System.UInt16":

strConvertedType = "ushort";

break;

case "System.String":

strConvertedType = "string";

break;

}//eof switch 

return strDotNetTypeString; 

#endregion DefineTheConvertedType

} //eof method 

public static string FromDotNetTypeToConvertToDotNetTypeCode(string strDotNetTypeString)

{

string strConvertTo = strDotNetTypeString;

#region DefineTheConvertedType

//<source>http://msdn.microsoft.com/en-us/library/ya5y69ds.aspx</source>

//<source>http://ysgitdiary.blogspot.com/2010/02/c-types-vs-net-types-code-generation.html</source>

switch (strDotNetTypeString)

{

case "System.Boolean":

strConvertTo = "System.Convert.ToBoolean";

break;

case "System.Byte":

strConvertTo = "System.Convert.ToByte";

break;

case "System.SByte":

strConvertTo = "System.Convert.ToSByte";

break;

case "System.Char":

strConvertTo = "System.Convert.ToChar";

break;

case "System.Decimal":

strConvertTo = "System.Convert.ToDecimal";

break;

case "System.Double":

strConvertTo = "System.Convert.ToDouble";

break;

case "System.Single":

strConvertTo = "System.Convert.ToSingle";

break;

case "System.Int32":

strConvertTo = "System.Convert.ToInt32";

break;

case "System.UInt32":

strConvertTo = "System.Convert.ToUInt32";

break;

case "System.Int64":

strConvertTo = "System.Convert.ToInt64";

break;

case "System.UInt64":

strConvertTo = "System.Convert.ToUInt64";

break;

case "System.Object":

strConvertTo = "System.Convert.ToObject";

break;

case "System.Int16":

strConvertTo = "System.Convert.ToInt16";

break;

case "System.UInt16":

strConvertTo = "System.Convert.ToUInt16";

break;

case "System.String":

strConvertTo = "System.Convert.ToString";

break;

}//eof switch 

return strDotNetTypeString;

#endregion DefineTheConvertedType

} //eof method 

} //eof class 

} //eof 

C# types vs. .Net types --> code generation with textpad

C# type	.Net Type
bool	System.Boolean
byte	System.Byte
sbyte	System.SByte
char	System.Char
decimal	System.Decimal
double	System.Double
float	System.Single
int	System.Int32
uint	System.UInt32
long	System.Int64
ulong	System.UInt64
object	System.Object
short	System.Int16
ushort	System.UInt16
string	System.String

Some regexes:
Build this table
Find:^([a-z]*)\t([a-z\.A-Z0-9]*)
Replace:
\1\2


Build switch case for each type:
Find:^([a-z]*)\t([a-z\.A-Z0-9]*)
Replace:\t\tcase "\2": \n\t\t\tstrConvertedType = "\1"; \n\t\t\tbreak;\n\n


Build the ConvertToCodeCalls:
Find: ^([a-z]*)\t([a-zA-Z0-9]*)\.([a-zA-Z0-9]*)
Replace:\t\tcase "\2.\3" : \n\t\t strConvertTo = "System.Convert.To\3" ;\n\t\t break;\n

Db versioning with DbScripter.exe

   1:    'COPY D:\Temp\Temp\POC_DEV\Programmability\StoredProcedures\' + 

   2:    ObjectName + '.sql D:\temp\temp\POC_DEV\1.2.8\StoredProcedures\'

   3:    + ObjectName + '.sql'

   4:   

   5:  FROM tbDataMeta_ChangeLog

   6:  where ObjectType = 'PROCEDURE' 

   7:  AND EventDate>'2010-02-10'

   8:   

   9:   

  10:   

  11:   

  12:   

  13:   

  14:  USE [MY_DB]

  15:  GO

  16:   

  17:  IF  EXISTS (SELECT * FROM dbo.sysobjects 

  18:  WHERE id = OBJECT_ID(N'[DF_EventsLog_EventDate]') AND type = 'D')

  19:  BEGIN

  20:  ALTER TABLE [dbo].[DbObjectsChangeStore] DROP CONSTRAINT [DF_EventsLog_EventDate]

  21:  END

  22:   

  23:  GO

  24:   

  25:  USE [MY_DB]

  26:  GO

  27:   

  28:  /****** Object:  Table [dbo].[DbObjectsChangeStore]    

  29:  Script Date: 02/11/2010 17:58:00 ******/

  30:  IF  EXISTS (SELECT * FROM sys.objects 

  31:  WHERE object_id = OBJECT_ID(N'[dbo].[DbObjectsChangeStore]') AND type in (N'U'))

  32:  DROP TABLE [dbo].[DbObjectsChangeStore]

  33:  GO

  34:   

  35:  USE [MY_DB]

  36:  GO

  37:   

  38:  /****** Object:  Table [dbo].[DbObjectsChangeStore]    

  39:  Script Date: 02/11/2010 17:58:00 ******/

  40:  SET ANSI_NULLS ON

  41:  GO

  42:   

  43:  SET QUOTED_IDENTIFIER ON

  44:  GO

  45:   

  46:  CREATE TABLE [dbo].[DbObjectsChangeStore](

  47:      [LogId] [int] IDENTITY(1,1) NOT NULL,

  48:      [DatabaseName] [varchar](256) NOT NULL,

  49:      [EventType] [varchar](50) NOT NULL,

  50:      [ObjectName] [varchar](256) NOT NULL,

  51:      [ObjectType] [varchar](25) NOT NULL,

  52:      [SqlCommand] [varchar](max) NOT NULL,

  53:      [EventDate] [datetime] NOT NULL,

  54:      [LoginName] [varchar](256) NOT NULL

  55:  ) ON [PRIMARY]

  56:   

  57:  GO

  58:   

  59:  ALTER TABLE [dbo].[DbObjectsChangeStore] 

  60:  ADD  CONSTRAINT [DF_EventsLog_EventDate]  

  61:  DEFAULT (getdate()) FOR [EventDate]

  62:  GO

  63:   

  64:   

  65:  IF  EXISTS (SELECT * FROM sys.triggers

  66:   WHERE parent_class_desc = 'DATABASE' AND name = N'trig_BackUpDbObjects')

  67:  DISABLE TRIGGER [trig_BackUpDbObjects] ON DATABASE

  68:   

  69:  GO

  70:   

  71:  USE [MY_DB]

  72:  GO

  73:   

  74:  /****** Object:  DdlTrigger [trig_BackUpDbObjects]   

  75:   Script Date: 02/11/2010 17:58:49 ******/

  76:  IF  EXISTS (SELECT * FROM sys.triggers 

  77:  WHERE parent_class_desc = 'DATABASE' 

  78:  AND name = N'trig_BackUpDbObjects')

  79:  DROP TRIGGER [trig_BackUpDbObjects] ON DATABASE

  80:  GO

  81:   

  82:  USE [MY_DB]

  83:  GO

  84:   

  85:  /****** Object:  DdlTrigger [trig_BackUpDbObjects]    Script Date: 02/11/2010 17:58:49 ******/

  86:  SET ANSI_NULLS ON

  87:  GO

  88:   

  89:  SET QUOTED_IDENTIFIER ON

  90:  GO

  91:   

  92:   

  93:   

  94:  create trigger [trig_BackUpDbObjects]

  95:  on database

  96:  for create_procedure, alter_procedure, drop_procedure,

  97:  create_table, alter_table, drop_table,

  98:  create_function, alter_function, drop_function

  99:  as

 100:   

 101:  set nocount on

 102:   

 103:  declare @data xml

 104:  set @data = EVENTDATA()

 105:   

 106:  insert into [dbo].[DbObjectsChangeStore](databasename, eventtype, 

 107:  objectname, objecttype, sqlcommand, loginname)

 108:  values(

 109:  @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),

 110:  @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), 

 111:  @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), 

 112:  @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 

 113:  @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 

 114:  @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')

 115:  )

 116:   

 117:   

 118:   

 119:   

 120:  GO

 121:   

 122:  SET ANSI_NULLS OFF

 123:  GO

 124:   

 125:  SET QUOTED_IDENTIFIER OFF

 126:  GO

 127:   

 128:  DISABLE TRIGGER [trig_BackUpDbObjects] ON DATABASE

 129:  GO

Why there is only one Silicon Valey

SOURCES:
Why there is only one Silicon Valey

how-to use counter in a single selecte statement - a row_number usage in tsql

SELECT 'SELECT ' + convert( nvarchar , (ROW_NUMBER() over(order by tbGuiTabWizardId) ) + 106) + 

', 51, N''~/tabwizard.aspx?pageid=' +

convert( nvarchar , (ROW_NUMBER() over(order by tbGuiTabWizardId) ) + 106) +  '&tabid=' +CONVERT(nvarchar ,  tbGuiTabWizardId) + ''' , 

N''' + TabTitle + ''''', N'''''', N'''', 0, 0 UNION ALL'   FROM tbGuiTabWizard