SQL Server 2014 – Run Powershell script from administrator as administrator

I need to recycle an AppPool in IIS on my server when the SQL Server service is started.

The path I take is a stored startup procedure that runs an agent job that includes a powershell job step.

I've created a Powershell script that reuses the app pool:

# Load IIS module:
Import module WebAdministration
# Specify a name of the site for which you want to recycle the pool:
$ site = "Default Web Site"
# Determine the pool name based on the site name:
$ pool = (Get-Item "IIS:  Sites  $ site" | Select-Object applicationPool) .applicationPool
# Recycle the application pool:
Restart WebAppPool $ pool

This works at the operating system level, but only if Powershell is running as an administrator (although the account I am logged into Windows with is the same as in the admin group).

Sure enough, if I create the job in Agent with a Powershell step using the above code, I get the error while running

A job step got an error on line 6 in a PowerShell script. The
The corresponding line is & # 39; $ pool = (get-item "IIS: Sites $ site" |)
Select-Object applicationPool) .applicationPool & # 39 ;. Correct the script
and relocate the job. The error information returned by PowerShell
is: & # 39; The dynamic parameters for the cmdlet can not be retrieved. Filename:
redirection.config Error: Configuration file can not be read due to
Insufficient permissions & # 39 ;. Process exit code -1. The step
failed.

So I need a way to run as an administrator from the SQL Server Agent

I succeeded at the operating system level by creating a new script that runs the original script with elevated privileges:

Unless ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]:: GetCurrent ()). IsInRole ([Security.Principal.WindowsBuiltInRole]::Administrator))
{
# Relaunch as an increased process:
Start process powershell.exe "-File", (& # 39; "{0}" & # 39; -f $ MyInvocation.MyCommand.Path) -Verb RunAs
exit
}
# Start now with increased execution, so start the script:
& "d:  path  to  my  script.ps1"

This allows me to run the new script from either a non-elevated CMD prompt or an unresolved Powershell command prompt. However, when I try to run the agent as a powershell or CMD step, I get it

The default permission settings for computers do not grant local activation
Authorization for the COM Server application with CLSID {longguidhere}
and APPID {longguidhere} for the user NT SERVICE SQLSERVERAGENT SID
(longguidhere) from Address LocalHost (Using LRPC) that runs in the
Application Container Unavailable SID. This security
Authorization can be changed using the Component Services component
Tool.

I tried too

Start-Process Powershell -Verb runAs -FilePath "d:  path  to  my  script.ps1"

which, in turn, worked at the OS level, but when run by the agent, gives me the same error.

How can I get that?

a: Recycle the app pool without requiring elevated permissions

or

b: Run the script to recycle the app pool with elevated permissions from the SQL Server Agent.

By mistake, Windows authentication disables SQL Server 2014

I disabled Windows authentication in SQL Server 2014.
I do not know how to reactivate it.
I tried googling but could not find a solution.
It means to extend security and then extend login. Right-click Administrator, and then click Properties. Click Status and check the box Login enabled.
At first, I can not connect because I no longer have access.
The second solution was through SQL CMD, but I can not open it either because the administrator is disabled

Migration – Can I disconnect Sharepoint 2007 databases from a SQL Server 2008R2 and connect to SQL Server 2014 without migrating Sharepoint?

I want to know if I can disconnect SQL Server 2008R2 Sharepoint 2007 databases (Windows Server 2008R2) and connect to SQL Server 2014 (Windows Server 2012R2) without migrating Sharepoint?

Assuming it is possible, I would do the following:

  • Stop Sharepoint Services and IIS on my WFEs
  • Disconnect the SharePoint DBs from SQL SERVER 2008R2
  • Attach all Sharepoint DBs to SQL Server 2014
  • Restart all Sharepoint services and IIS

Does anyone have any recommendations?

tks
Kenny

json – JSON_VALUE in SQL Server 2014

I am looking for equivalent functionality of JSON_VALUE in SQL Server 2014. Can any of you post an alternative of the following code that is supported in SQL Server 2014?

SELECT JSON_VALUE (ERROR_MSG, & # 39; $[0].applicationName & # 39;) AS APPLICATION_NAME
FROM table name

When running SQL 2014, the following is displayed:

& # 39; # 39 & JSON_VALUE; is not a recognized name of a built-in function.

sql server 2014 – Encrypt file system for FileStream folder _ Use existing free space for EFS processing

Can anyone help me below when it comes to EFS for File Stream folder.

I am connected to SQL Server 2014 SP2 and Availability Groups on Windows Server 2012 R2 Standard.

I have 800 GB FileStream folder. While I try to apply EFS to 800 GB, it consumed additional 200 GB of data just for processing. I would like to understand things that someone who has implemented a similar scenario like mine or encountered me helps me to understand:

  1. Does EFS processing for the FileStream folder require extra space for processing?
  2. Is the space automatically restored after EFS processing?
  3. If not, is there a way to free up the used space?
  4. Can I shrink FileStream data as well? I've learned that only garbage collection can clean up the un-locked space of File Stream! Please let me know if I can downsize and make room.

SQL Server 2014 – Allow a user to grant SELECT and VIEW DEFINITION permissions only to objects that they create

How do I give access to a user so that he can grant it? CHOOSE and DISPLAY DEFINITION for all objects that you create in a database.

For example, I have a user MyUser in the roles

db_datareader
db_datawriter
db_ddladmin

and this user can create a table:

CREATE TABLE Test123
(
an INT
)

but I would like to grant the user then CHOOSE and DISPLAY DEFINITION just on the table you just created (and all the others you create when you create them)

GRANT SELECT ON Test123 TO [AnotherUser]
GRANT VIEW DEFINITION ON Test123 BIS [AnotherUser]
GO

What are my options to achieve this?

I can only imagine that the user creates objects in a schema and has them as the owner of the schema. Ideally, I want the objects to be created in the current default schema of the user

Overrun – Applying for a UK visa within 28 days of the expiration of the previous visa in 2014, it will be overrun

I apply for an Australian dependent visa in 2019 and I doubt I stayed too much in the UK in 2014. I applied for a business visa within 28 days of the grace period, but this was denied in February 2015.

Then I appealed against the decision. However, as it took longer than expected, we decided to withdraw the complaint and voluntarily leave the UK in July 2015. I was told that no ban was imposed. When I applied for a British tourist visa in October 2015, my visa was denied for financial reasons not due to an earlier ban or for any reason.

So the question is, if I got over in the UK? What should I mention now in my Australian visa? I am really confused and the UK Immigration Department does not provide clear information and different lawyers suggest different answers. I'm deeply worried, so I'll be grateful if someone could help me. My wife is in Australia and I have to move in with her as soon as possible

SQL Server 2016 – Upgrade of SQL 2014 AG to SQL 2017 AG

Can SQL 2014 AG (Windows 2012, SQL 2014) be migrated to SQL 2017 AG (Windows 2016, SQL 2017) using the rollup method?

What I mean is

  1. 2 create SQL 2017 instance.
  2. Add the above nodes to the existing cluster.
  3. Add the new nodes to the existing AG as an additional replica (async)
  4. Failover on 2017 server
  5. Remove the 2014 node from Ag
  6. Remove the 2014 nodes from the cluster.

Would that work or would we have to build everything from scratch?

Many Thanks

SQL Server 2014 – CheckDB error in most databases in the dev instance

I have a maintenance plan for one of our seldom-used developer boxes, which fails CheckDB for all user databases and most system databases over and over again. When trying to run CheckDB manually, I get the following message. I tried to create a database snapshot and run CHECKDB from here. However, I am working with the Standard Edition SQL 2014, so an error message is displayed indicating that database snapshots are not supported in this release.

Message 5030, Level 16, State 12, Line 1
The database could not be locked exclusively for the operation.
Message 7926, Level 16, Status 1, Line 1
Check if the statement was canceled. The database could not be verified because no database snapshot could be taken and the database or table could not be locked. See the online documentation for details on when this behavior is expected and what workarounds exist. Further information can be found in the previous errors.

If Model and TempDB have been executed manually, checkdb fine-tuning is completed. However, problems have occurred in all other databases. I have other instances on the same server that complete their checkdb routines without any problems. So it seems that the respective instance is something isolated.