How to script SQL server database role permissions

SELECT 'GRANT ' + database_permissions.permission_name +

    CASE database_permissions.class_desc

        WHEN 'SCHEMA' THEN ' ON ' +schema_name(major_id)

        WHEN 'OBJECT_OR_COLUMN' THEN

            CASE WHEN minor_id = 0 THEN ' ON ' +object_name(major_id) COLLATE Latin1_General_CI_AS_KS_WS

            ELSE ' ON ' +(SELECT object_name(object_id) + ' ('+ name + ')'

                  FROM sys.columns

                  WHERE object_id = database_permissions.major_id

                  AND column_id = database_permissions.minor_id) end

        ELSE ''

    END +

    ' TO ' + database_principals.name COLLATE Latin1_General_CI_AS_KS_WS

FROM sys.database_permissions

JOIN sys.database_principals

ON database_permissions.grantee_principal_id = database_principals.principal_id

LEFT JOIN sys.objects --left because it is possible that it is a schema

ON objects.object_id = database_permissions.major_id

WHERE permission_name in ('SELECT','INSERT','UPDATE','DELETE','EXECUTE') AND database_principals.NAME = ''

How can I see what certificates are installed on a Windows computer with PowerShell

Using PowerShell to view certificates is easy. PowerShell has a provider that exposes the certificates store which is part of the pki and security modules, which are loaded automatically as long as you’re on version 3 or greater. You do not need to manually load the modules, they auto-load from PowerShell v3 and above.

To view the certificates in the local users personal certificate store/local machine store I would use the following:  

#Change to the location of the personal certificates

Set-Location Cert:\CurrentUser\My

#Change to the location of the local machine certificates

Set-Location Cert:\LocalMachine\My

#Get the installed certificates in that location

Get-ChildItem | Format-Table Subject, FriendlyName, Thumbprint -AutoSize

Determine the file size of a database using t-sql

Determine the file size of a database using t-sql

In SQL Server there are a number of ways to determine how large a database is. Right clicking and selecting properties > files in management studio for example is probably the easiest. However if you do not have very high permissions on the database you are interested in this t-sql may be what you need. 

This script requires the least permissions of all the different methods I could find and is fairly simple which is a bonus.

select a.FILEID,
      [FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)),
      [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
      [FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)),
      NAME = left(a.NAME,15),
      FILENAME = left(a.FILENAME,30)
from dbo.sysfiles a

Tested in SQL Server 2005+ 

Execute all SQL Scripts in a Folder

How to quickly execute a collection of SQL Scripts against a SQL Server Database.

The simplest solution I have found is to use a quick batch script and leverage the osql command. The osql utility is a Microsoft® Win32® command prompt utility for ad hoc, interactive execution of Transact-SQL statements and scripts.


To use the script you will need to:
1. download it from here, This script will execute every .sql in the folder it is executed in.
2. open it up in a text editor of your choice and substitute your connection information for the dummy "[Your XX]" placeholders.
3. Close the file and double click on it to execute.

RunScript.bat

FOR %%x IN (*.sql) DO OSQL -U [Your UserName] -P [Your Password] -S [My Server] -d [My Database] -i "%%x" >> Log.txt

%%x is a variable for the current file. (*.sql) is what selects the files in the folder. Everything after the Do is what is executed for every file.

Note: scripts will execute in alphabetical order. As a bonus the script logs the output to Log.txt

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)