Posts

Showing posts from August, 2022

Database stuck in Synchronized\Recovery Pending state.

I faced this issue in one of my client production environments . Databases are configured on AOAG. But one day one of the databases have gone into SYNCHRONIZED /RECOVERY PENDING state   We tried to check the progress of this database in READERROR log of  that particular database, in the error log it says 99% completed , after that there is no progress. At the same time we don't see any REDO Log Commit LSN or SEND LSN values under AOAG dash board.  Dont remove the database from AOAG and re added AOAG again. Simply run the below command and it did the trick. USE master go ALTER DATABASE DatabseName SET ONLINE

GRANT VIEW or GRANT VIEW ANY PERMISSION

/* This permision(SP_HELPTXT) is only on those particular databases and those particular logins only */ USE Database1 GO GRANT VIEW DEFINITION TO [ Domain \ User1] GRANT VIEW DEFINITION TO [ Domain \ User2] go USE Database2 GO GRANT VIEW DEFINITION TO [ Domain \ User1] GRANT VIEW DEFINITION TO [ Domain \ User2] /* Granting VIEW(SP_HELPTEXT) permissions to on ALL databases in that server and to this account only */ USE master GO GRANT VIEW ANY DEFINITION TO USER1 The below command will give ALTER,CONTROL and VIEW DFFINITION permissions on a sepcific database. USE Test GO /* The below commands will give you Modify or ALTER Stored procedure and see the text of the logical objects which are created with dbo */ GRANT ALTER ON SCHEMA ::dbo TO LoginName GRANT CONTROL ON SCHEMA ::dbo TO LoginName GRANT VIEW DEFINITION ON SCHEMA ::dbo TO LoginName

EXEC sp_msforeachdb

 Below is the example. The below query will change the owner of all the databases to sa at a time sp_msforeachdb @ command1 = 'USE ?;IF DB_ID(''?'') > 4 begin EXEC sp_changedbowner ''sa'' end' Getting all the table information at time sp_msforeachdb @ command1 = 'USE ?;SELECT * FROM sys.Tables'

Export-DbaLogin, dbatools

Image
After running this script also check for Orphan users once. <# This will script out all the login(server principals and user permissions(database principals) to the below mentioned file in temp folder of C drive. After run this script in respective destination server also please check orphan users once. #> Export-DbaLogin ` -SQLInstance RAMESH\PROD ` -OutFile 'C:\temp\RAMESHPROD_LOGIN_USER_Permissions_08082022.txt' ` -Verbose The below piece of code will automatically generate the time stamp with server database details. $servername = "myServerNameHere" $dbName = "dbNameHere" $date = Get-Date Export-DbaLogin ` -SqlInstance $servername ` -Database $dbName ` -Verbose ` -FilePath "C:\AzureBackpack\ $( $ servername) _ $( $ dbName) _ $( $ date.ToString("yyyyMMdd_HHmmss") ).txt" The below command will script out individual login level permissions. $instance = "MAPS" $date = Get-Date -Format ...

Running queries with OSQL.EXE

Image
Sometimes we can run SQL queries with a tool called OSQL.EXE . This is available in the below path by default based on SQL Server version. D:\Program Files\Microsoft SQL Server\140\Tools\Binn\OSQL.exe.  See the below image. By using the OSQL tool I have a run a SELECT query.  -S ServerName or InstanceName -i   Path in which I saved the .SQL query which contains SELECT command. And I      saved it in a  folder of a drive. -E  Windows Authentication -U  SQL Authentication. -P Password need to be provided if you choose SQL Authentication(-U). Below case I have chosen Windows Authentication And my select query looks below. And I save this to AdventureWorks2012 folder of D drive.  USE AdventureWorks2012 GO SELECT top 10 * FROM [HumanResources].[Department] Go to Run-->Cmd--> and here is the query and double quotes("") are compulsory If you want to export above result set to text file pass a parameter called -o and prov...