Posts

Showing posts from November, 2020

Coloring a particular a Row result with PowerShell

cls $services = Get-Service -name '*sql*' $services | ForEach -Object{ $Know = $_ if ( $_ .Status -eq "Running" ) { Write-Host $Know .Status, $Know .Name, $Know .DisplayName -foregroundcolor Green -Separator ' : ' } else { Write-Host $Know .Status, $Know .Name, $Know .DisplayName -foregroundcolor Red -Separator ' : ' } }

Adding members to Active Directory Login Groups in SQL Server through powershell

Image
 By using the below command lets we can add members to the active directory login groups. While Providign Group name dont provide the Domain name group alone works. <# The below Windows feature command will import Poweshell Module Add-WindowsFeature #> Add-WindowsFeature RSAT-AD-PowerShell Import-Module ServerManager import-module activedirectory <# Space is compulsory between FirstName and LastName else it throws error Depends on requirement you can choose like(-like) or equal(-eq) commands. #> Get-ADUser -Filter 'Name -like "*Ramesh, Mamillapalli"' Add-ADGroupMember -Identity "GroupNameHere" -Members ( Get-ADUser -Filter 'Name -like "*Ramesh, Mamillapalli"' ) Get-ADUser -Filter 'Name -like "*MamilRam*"' The below query fetch group members from active dirctory domain group cls $GroupName = 'GroupNameHere' Get-ADGroup -filter *|sort name| Where-object { $_ .Name -eq $GroupName...

Migrating database level permissions(users and their permissions) from Source to Destination in sql server

The below script need to run based on the requirement for instance if you are restoring a production database in UAT environment for testing purpose follow these instructions 1) Run the below script in UAT environment and capture the user info and save it to notepad 2) Then RESTORE production database in UAT environment 3) Then run the script that we captured in the first script. NOTE: Even after running the below script there is a possibility that you can find orphaned users , so try to run  EXEC sp_change_users_login 'Report'; and fix the orphans users if any exists. There is another powershell command which is available in dbatools, to use the below command first you need to import or install dbatools . DECLARE @ sql VARCHAR ( 2048 ) , @ sort INT DECLARE tmp CURSOR FOR /*********************************************/ /********* DB CONTEXT STATEMENT *********/ /*********************************************/ SELECT '-- [-- DB CONTEXT --] --...

Which login belongs to which active directory group, handling through xp_logininfo.

  Run the first piece of code with in the comment section first. This will generate print statement of  xp_logininfo. Copy that print statement and paste it in the second pieced of code in between ' ' otherwise it throws error. /* DECLARE @Stmt NVARCHAR(4000) set @Stmt='SELECT ''EXEC master..xp_logininfo ''+''''''''''''+Name+''''''''''''+'',''+''''''''''members'''''''''' FROM sys.server_principals where [type]=''G''' EXEC sp_executesql @Stmt */ declare @ STMT2 VARCHAR ( 4000 ) set @ stmt2 = ' EXEC master..xp_logininfo ''domain1\group1'',''members'' EXEC master..xp_logininfo ''domain1\grop2'',''members'' EXEC master..xp_logininfo ''domain1\group3'',...

Exclusive access could not be obtained because the database is in use_OFFLINE_ONLINE

Usually I used to get this type error while i am trying to RESTORE databases. So to avoid these type of errors permanently we need to write RESTORE command in between changing SINGLE_USER and MULTI USER statements. See below command. Msg 3101, Level 16, State 1, Line 2 Exclusive access could not be obtained because the database is in use. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally. Msg 3117, Level 16, State 1, Line 3 The log or differential backup cannot be restored because no files are ready to roll forward. Msg 3013, Level 16, State 1, Line 3 RESTORE DATABASE is terminating abnormally. Don't use GO command between the below statement run it as a single script. USE [master] ALTER DATABASE [Adventureworks] SET SINGLE_USER WITH ROLLBACK IMMEDIATE ---RestoreCommand between these two statements. RESTORE DATABASE Adventureworks FROM DISK = 'D:\Adventureworks_full.bak' WITH NORECOVERY ALTER DATABASE Adventureworks SET ...

process terminated unexpectedly error 1067

I got this error in one of my clients environment where they are migrating their environment from VMWare to Hyper-V. They have migrated Hyper-V but the SQL Server Analysis services are not up and running, When we try to START the services it is throwing below error and the Services are not coming online, due to which even Windows Failover Cluster Roles and Services are OFFLINE state, and restarting the server is also not worked, moving nodes also not worked The main culprit here is DRIVES, drives are not migrated and in my environment DRIVE LETTER GOT CHANGED in which SQL Server Analysis configured.  You can check the drive information of Analysis Services in the start up parameters of SQL Server Analysis services, either this could be from SQL Server Configuration Manager or from Servicse.msc. After that made change to the respective drive from diskmgmt.msc and it is fixed the issue. "process terminated unexpectedly error 1067"