Update datatype of primary key in SQL Server table

Goal

To generate the tsql commands that we can use to alter the primary key datatype.

My way of achieving it

Using powershell to check some of the relations between the tables. As with all other things that I am posting here, this, is far from being a final script. It is just something that you can use in order to check the level of work required to change the data type of a primary key. Sometimes in our job we are asked to perform this type of activity and although it seems like a trivial task there are hidden things that make it very difficult. I tried to show as many things that I could for which the change of datatype might fail but I am sure that depending on the database structure the script will still fail even after we will perform the steps described in the output of the script.  The output of the script must be followed and changed accordingly to your requirements. In the script I changed the data type of InqNo column from int to numeric(18,0).

The script requires the instance name, database name and table name

$instancename = “UCPHMA”
$databasename = “Inquiry2012”
$tablename = “Inquiries”

The output of the script can be seen below and as you can see asks for dropping and recreating some indexes among other things.

IMP – Those actions needs to be very good planned when it comes to the production servers and big tables due to all the problems that dropping an index creates and all the problems that creating an index creates. But in case the tables are small then the impact will not be big. As with every script, first needs to be tested in a controlled environment and used only after the operation was successful there.

Very important is to check the script output before executing it.

The script can be downloaded from here

Migrate, re-create login(s) in SQL Server using Powershell and script method

Goal

To generate the sql server login with a single click in case we want to migrate it or just want to see what permissions it has on our instance.

My way of achieving it

Using powershell and the work of other people over the internet :). My desire was to have the code generated while I am right clicking on the Logins folder or a Login in SSMS and start powershell and paste a script. Although over the internet I found a lot of examples, the one that caught my eyes was this post (http://jongurgul.com/blog/sql-server-instance-security-scripting-permissions/) . It seems that the author wanted also the same thing but he changed his approach.  — This script was written initially to allow permissions to be scripted out with a single click,  —

Because I still wanted to create and to make a script for that approach I inspired myself and came with the script that will just give me that information. Far from being a final script, this version has the advantage that you can generate the info for all of the logins or only for one login and the information is in this order

  • create login
  • server roles
  • server permissions
  • object permissions at server level
  • create user for that login for the mapped databases
  • database roles
  • database permissions for that login
  • object permissions for that login

An output of running the script looks like

The script can be downloaded from here.