Grant exec on all stored procs

Its so annoying that sql server doesn't come with a default role to just have exec permissions on all stored procs. Oh well.

I love this little stored proc. Its usually a good idea to prevent your database user from having direct read/write access to your database to tricks some nasty hacker might throw at you - Its usually good practice to give your user just access to the stored procedures. I usually run it at the end of every release to ensure the user can exec any stored proc.

use master
go
create procedure sp_grantexec(@user sysname,@pattern sysname = NULL,@debug int = 0)
as
set nocount on
declare @ret int
declare @sql nvarchar(4000)
declare @db sysname ; set @db = DB_NAME()
declare @u sysname ; set @u = QUOTENAME(@user)


set @sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @u + ''' FROM INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'

if @pattern is not null
set @sql = @sql + N' AND ROUTINE_NAME LIKE ''' + @pattern + ''''

if @debug = 1 print @sql
else
exec @ret = master.dbo.xp_execresultset @sql,@db

If @ret <> 0
begin
raiserror('Error executing command %s',16,1,@sql)
return -1
end

Script to kill all connections to a database

When restoring/moving databases its a pretty common requirement to kill all the connections to the database in question otherwise you can't get a lock on it. Killing all the connections to a database can be a pain though if you have to use the UI. This script is much quicker and can be a time saver. If you’re super keen you could modify the script to make it a stored procedure so it’s always ready to go. If you do want to use the UI here is a blog post on how to do it.

--
-- Kill connections to a given sql server database
--

declare @execSql nvarchar(MAX), @databaseName varchar(100)
set @databaseName = '<your database name here>'

set @execSql = ''
select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from master.dbo.sysprocesses
where db_name(dbid) = @databaseName
and
DBID <> 0
and
spid <> @@spid
exec(@execSql)

A better way to declare your ValueConverters in XAML

Generally most people define a resource like this "<conv:AdditionConverter x:Key="AdditionConverter" />" and then use the converter like this "{Binding Converter="{StaticResource NegatingConverter}} "

This is OK but it’s fairly verbose and leads to converters being defined all over the place. An alternative is to use a MarkupExtension to minimize the amount of XAML code required. E.g.:

    public class AdditionConverter : MarkupExtension, IValueConverter

    {

        private static AdditionConverter _converter;

        public object Convert(object value, Type targetType,

        object parameter, System.Globalization.CultureInfo culture)

        {

            // convert and return something

        }

        public object ConvertBack(object value, Type targetType,

        object parameter, System.Globalization.CultureInfo culture)

        {

            // convert and return something (if needed)

        }

        public override object ProvideValue(IServiceProvider serviceProvider)

        {

            if (_converter == null)

                _converter = new AdditionConverter();

            return _converter;

        }

    }

Doing this you no longer need to define a resource as the XAML parser knows how to ask the markup extension to supply the converter. You end up with a syntax like this:

{Binding Converter={conv:MyConverter}} 

This approach has an added advantage of ensuring that all your converters are singletons.

Taking it further: Making a wrapper base class

This article explains a generic markup extension class that hides the MarkupExtension implementation details and makes the process above even easier.

ConverterMarkupExtension.cs

using System;

using System.Windows.Data;

using System.Windows.Markup;

namespace sfc.Converters

{

    [MarkupExtensionReturnType(typeof(IValueConverter))]

    public abstract class ConverterMarkupExtension :

    MarkupExtension where T : class, IValueConverter, new()

    {

        private static T _converter;

        public override object ProvideValue(IServiceProvider serviceProvider)

        {

            if (_converter == null)

            {

                _converter = new T();

            }

            return _converter;

        }

    }

    [MarkupExtensionReturnType(typeof(IMultiValueConverter))]

    public abstract class MultiConverterMarkupExtension :

    MarkupExtension where T : class, IMultiValueConverter, new()

    {

        private static T _converter;

        public override object ProvideValue(IServiceProvider serviceProvider)

        {

            if (_converter == null)

            {

                _converter = new T();

            }

            return _converter;

        }

    }

}

There are 2 classes here, one for IValueConverter's and one for IMultiValueConverters.

You can then modify your classes to inherit from this class and they nolonger need to worry about implementing MarkupExtension E.g.:

    public class AdditionConverter : ConverterMarkupExtension<AdditionConverter>

    {

        public object Convert(object  value, Type targetType, 

        object  parameter, System.Globalization.CultureInfo culture)

        {

            // convert and return something

        }

        public object  ConvertBack(object value, Type  targetType, 

        object parameter,  System.Globalization.CultureInfo culture)

        {

            // convert and return something (if needed)

        }

    }

How to login to sql server using windows authentication over a VPN

This only applies to people that want to connect to a SQL Server instance over VPN to a domain that doesn't have a trust relationship with the source domain and don't want to have to use remote desktop. The method described below will allow you to access the SQL database seamlessly as if it were a database insist your domain.

1) Open a command prompt window

2) runas /user:"domain\user" /netonly "c:\program files\microsoft sql server\100\tools\binn\vsshell\common7\ide\ssms.exe" - replacing "domain\login" with your login details obviously

3) The command prompt with ask for "domain\login"'s password

4) SMSS will then run and you can connect to the database over VPN using the Windows Authentication option.