Tuesday, 27 June 2017

Run SSMS As User in Different Domain

Every come across the trouble of having to login to a jump server in just to access SSMS since the server is in different domain. There is a way to by pass it and access SSMS using your second domain login from your desktop by using Runas /NetOnly option irrespective of domain trust.

Use the below command in the Command Prompt:-

runas /netonly /user:<domain\username> "<SSMSbasepath>\ssms.exe"

SSMS base path is the folder where your SSMS.exe is located.

More Info https://technet.microsoft.com/en-gb/library/bb490994.aspx




Tuesday, 30 May 2017

Find SSAS instance Port Number

When you have more than one instance of SSAS installed in your Server you might end up in a situation to find the port numbers for each instance to set the firewall settings on the server. As you might all know the default Port for SSAS is TCP 2383, 

To find the port number of the other instances search for the msmdredir.ini file on your ProgramFiles(X86)->Microsoft SQL Server folder, most probably the file resides in the (%ProgramFiles(X86)%\Microsoft SQL Server\90\Shared\ASConfig\msmdredir.ini)location, Look in both 64 bit and 32 bit Program Files folder but more likely to be in 32 bit folder even for 64 bit installations.

If you have more than 1 Instance you will have entry in the ini file like below, If you change the port number to 0 it would take dynamic ports else you can assign a static port to the instance.

<Instances>
<Instance>
<Name>InstanceName</Name>
<Port>64931</Port>
<PortIPv6>64931</PortIPv6>
</Instance>

</Instances>

The Above exercise will hold good only for Non-Clustered SSAS instance, Clustered SSAS instance always uses TCP Port 2383 with the corresponding SSAS Cluster Resouce IP.

Thursday, 23 March 2017

SQL Azure database - dbmanager and loginmanager role

Recently I was working for a client to install a new application with a backend on SQL Azure (Paas), The application was an inbuilt application which creates a database during application and does not have an option of using existing database, They did not want to use the Server Admin Login to accomplish this task. Unlike traditional SQL Server you can configure only one server Admin in the Azure Portal for SQL Azure Database as below.



And Since we do not have a server level entity in Azure we do not have any server level roles to provide a user with dbcreator. There alternate way to accomplish this according to MSDN( https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins ) there are two 2 new roles in the master database for SQL Server (dbmanager and loginmanager)


'dbmanager' allows users to create databases in Azure environment and loginmanager as the name suggests allows you to create new login without being a serveradmin. All you need to do is to create a login and corresponding user in master and grant it dbmanager role for the user to get dbcreator rights on SQL Azure, once we did this we were able to install the application without any issues and the new DB was created successfully.

Tuesday, 21 February 2017

Quick Tips:- Add SQL User/Rolemember to all Databases in SQL Server

Most of times when you have dedicated SQL Server for an Application clients come up with a request to add a user to all databases in the SQL Server with certain permission, while its east to do it via SSMS for if the system has only couple of databases but imagine your SQL instance have hundreds of databases then scripting is the way to go, I have assumed the login name is ‘Test’ client has request db_datareader and db_datawriter permission for our example blog,

There are 2 ways I am going to accomplish this one using TSQL and another using Powershell.

TSQL:-
--Add User
EXEC sp_MSforeachdb 'USE ?;CREATE USER Test FOR LOGIN Test'
GO
--Add Role member
EXEC sp_MSforeachdb 'USE ?;ALTER ROLE db_datareader ADD MEMBER Test'
GO
--Add Role member
EXEC sp_MSforeachdb 'USE ?;ALTER ROLE db_datawriter ADD MEMBER Test'

Powershell:-

Clear-Host

Import-Module SQLPS -DisableNameChecking -ErrorAction SilentlyContinue
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "1MKBG12"
$dbs = $srv.databases


$loginname = 'Test' ##########Provide Login name to check#############


$chkloginflag = $null
$chkloginflag = $srv.Logins|where {$_.name -eq 'Test3'}
if($chkloginflag -eq $null -or $chkloginflag -eq '')
{
      $login = New-Object ('Microsoft.SqlServer.Management.Smo.Login') ($srv, $loginname)
      $login.LoginType = 'SqlLogin' #https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.logintype.aspx
      $login.PasswordExpirationEnabled = $false
      $Login.PasswordPolicyEnforced = $false
      $login.Create("test")

      forEach($db in $dbs)
      {
            $usr = New-Object ('Microsoft.SqlServer.Management.Smo.User') ($db, $loginname)
            $usr.create()
            $usr.AddToRole("db_datareader")
            $usr.AddToRole("db_datawriter")
      }
}


The above PowerShell script also creates a Login if it does not exist then creates the SQL user in all databases with reader and writer roles. I have written a simple script assuming the Login to be SQL Authentication. You can tweak the $Login object to create Windows User/Group Login.