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.

Leave a Reply

Your email address will not be published. Required fields are marked *