Create SQL logins and add SQL Database roles

The Quest

Recently a friend of mine asked me for help. He has a few SQL servers with a bunch of DBs for their LOB apps. Some of those apps require direct permissions assignment as the apps do not work with Windows Groups (duh!). When a new employee joins a department he needs to be granted access to several DBs with either datareader/datawriter or db_owner role. He wanted to use Windows Account (Active Directory to be more specific). Clickingy-click was not an option anymore.

The Hunt

So I went for a hunt on the web. I looked for a neat solution that would be talkative if needed. He is still in the beginning of his PoSH journey. The caveat was not to use T-SQL 🙂 My personal restriction here – too much to learn for a newcomer – PowerShell and T-SQL at once.
I found some tips here and there. Most useful were those from Adam Bertram ( t | w ) (here) and Rob Sewell ( t | w ) (here). Yet it still wasn’t the thing I was looking for.

The Recipe

First I had to write down what I wanted to achieve:

  1. Add windows login as user to SQL server
  2. Grant windows login specific server rights (i.e. backupoperator)
  3. Add windows login to a database with specific permissions
  4. Add windows group to sql server
  5. No-TSQL
  6. Verbosity

If not T-SQL then how to achieve my goals? I’ve tried with native SQL PS provider but it didn’t do all I wanted. SMO won 😉 Got some basic points covered. But why not go a little bit further? What if someone makes a mistake while typing a server role? What if he forgets what is the SQL instance name on this specific server? Some basic checks would be nice.

During the process I’ve decided to split this into two functions. One should add a login to SQL server. The second one should add permissions on a specific DB. New-SQLLogin and New-SQLDBLogin can be found on my GitHub.

New-SQLLogin

Purpose of this function is to create a new Windows User or Windows Group login on SQL server and assign SQLRole if provided. It is using the following parameters:

$Computername – computer with SQL server installed
$SQLInstance – SQL instance. If not provided will enumerate and use default one if possible
$Identity – the account we want to add to a DB. It needs to exists on the SQL server already
$LoginType – currently only WindowsGroup and WindowsUser are valid
$SQLRole – defaults to public. Basically a public user is required on the sql server with specific permissions on given DB only. Though can be used to assign sysadmin role if needed

For this function to work, I’m starting with loading SMO assembly – this is a requirement on a machine that will run these functions.

If all is good the fun part begins. The logic of this function is quite simple:

  • If no $SQLInstance parameter is provided, it will try to enumerate sql instances
    • if no InstanceName property is found then it means a default one is used, so $ComputerName will be our Instance name
    • if there are more than two instances found – it will break with a warning
    • else it will use the found instance.
  • If $SQLInstance is provided – it will be used
  • Then I’m checking if a user already exists on the server. If not, I’m creating one
  • If no $SQLRole was provided, then public is used and nothing is required to do. A newly created user will have public role assigned
  • If $SQLRole was provided and it was a valid role – a user ($identity) is assigned to this role

For better readability, I’ve stripped the code from all messages. Full code can be found on my GitHub.

New-SQLDBLogin

Purpose of this function is to add existing user to SQL DB with specified database. It is using these parameters:

$Computername – computer with SQL server installed
$SQLInstance – SQL instance. If not provided will enumerate and use default one if possible
$Identity – the account we want to add to a DB. It needs to exists on the SQL server already
$Database database we will be working on
$DBRole – db role we wants to assign to the identity
This function also requires SMO assembly. Logic is similar to the one above:

  • If no $SQLInstance parameter is provided, it will try to enumerate sql instances
    • if no InstanceName property is found then it means a default one is used, so $ComputerName will be our Instance name
    • if there are more than two instances found – it will break with a warning
    • else it will use the found instance
  • If $SQLInstance is provided – it will be used
  • If provided identity is invalid or doesn’t exist on the server – function will terminate
  • Then check for DB is made – if it doesn’t exists – function will terminate
  • Another check for a DB role – and in case of error – function terminates
  • And finally provided identity is granted with necessary permissions

For better readability, I’ve stripped the code from all messages. Full code can be found on my GitHub.

Summary

Both functions are very talkative if -Verbose mode is used. I find it very useful to have every step clearly defined during the process. Also, verbose comments serve as an in-code documentation which can be easily reviewed in time.

Grab them from my GitHub. They are a part of ArcAdminTools module, but can be used independent. You can also install the module directly from PowerShell Gallery:

This may not be the perfect example of a code, but it serves its first and second purpose.

#1 Do the job

#2 Teach PowerShell to a new acolyte.

If you have any comments – feel free to contact me.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s