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

Comments

Post a Comment

Popular posts from this blog

Always On FailOver Events

Image
We can run below PowerShell and SQL Server commands when we want to find ou when the failover has happened. cls   Get-winEvent -ComputerName   ListerNameHere   -filterHashTable @{logname = 'Microsoft-Windows-FailoverClustering/Operational' ; id = 1641 } |   Where-Object { $_ . Timecreated -like '*05/09/2020*' } | Format-Table -AutoSize -Wrap <# 35201-->timeout alert run this by changing -LogName to System 41-->41 is power shutdown run this in system #> clear Get-EventLog -LogName Application -After ( Get-Date ) . AddHours( -48 ) | Where-Object { $_ . EventID -in ( 1100 , 1074 , 35201,41,1561,7024,1135,7032,1146 )} | Format-Table -AutoSize -Wrap cls  Get-EventLog ` -LogName System |Where-Object{$_.EventID -in (1054,1053,1055,1138,1141,1142,5140,5142,1559,1560,1563,1588,1068,5144,1562,5143,5168,1564,1177)}|Format-Table -AutoSize -Wrap EXEC sp_readerrorlog 0,1,'lease worker' EXEC sp_readerrorlog 0,1,'...
Read more

SSAS Cube or Database backup with Powershell command "Backup-AsDatabase"

Image
Run the full code from Load assembly section. # Load the assembly #[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | Out-Null # Create the server object $server = New-Object Microsoft.AnalysisServices.Server $server . Connect ( "localhost" ) # Or your SSAS instance name #Date Converion with minutes and seconds $date = ( get-date ).ToString( "MM_dd_yyyy_hh_mm_ss" ) $Server = New-Object Microsoft.AnalysisServices.Server $Server .Connect( "MAPS\TEST" ) $dbservers = $Server .Databases| Select-Object Name,ParentServer $dbservers | ForEach -Object{ $inddb = $_ #The "Backup-AsDatabase" is especially for SSAS cube backup and downloaded from "SQLServer" powershell module Backup-ASDatabase -BackupFile "E:\MAPS_TEST\SSAS_BACKUP\NEW\ $( $ inddb.Name) _ $( $ date) .abf" -Name $inddb .Name -Verbose -Server $inddb .ParentServer Write-Host "SSAS cube backup [ $( $ i...
Read more

The transaction log for database is full due to 'OLDEST_PAGE'

When I see the 'OLDEST_PAGE' in log_reuse_wait_desc column and not allowing to me to shrink the log file of database I followed the below steps 1) When you see 'OLDEST_PAGE' run CHECKPOINT on that particular database 2) Take the Transaction log backup of that particular database 3) Try to shrink the log file of database These steps worked for me when I encountered this issue in my environment.
Read more