SQL Server Advanced

T-Sql Advanced

 

Enable CLR, ‘Ole Automation Procedures’ or ‘xp_cmdshell’

What does this do?:

EXEC master.dbo.sp_configure  'show advanced options', 1;
GO
RECONFIGURE;

Using sp_configure

We need RECONFIGURE as it updates running value (run_value) also, without which it will not change run_value.
The other option to get updated run_value apart from RECONFIGURE is to stop and start the Server, which is not advisable.
Also some of the options will not update the run_value even after using RECONFIGURE

Jan: “I think we need ‘show advanced options to be able to change the advanced values. Like ‘clr enabled’‘.

 

 

-------------------------------------------------------------------------------------
-- Enable  Ole Automation Procedures
-------------------------------------------------------------------------------------

EXEC master.dbo.sp_configure  'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC master.dbo.sp_configure  'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

-------------------------------------------------------------------------------------
-- Enable  xp_cmdshell 
-------------------------------------------------------------------------------------

EXEC master.dbo.sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

-------------------------------------------------------------------------------------
-- Enable  CLR 
-------------------------------------------------------------------------------------

EXEC master.dbo.sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC master.dbo.sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

 

 

---

---

 

 

Which permissions are given to assemblies:

select * from sys.assemblies

Like: EXTERNAL_ACCESS, SAFE_ACCESS, UNSAFE_ACCESS.

Apply with this:

CREATE ASSEMBLY [MyAssemblyName]
FROM 0x4D5A90000300somelongstuff..
WITH PERMISSION_SET = EXTERNAL_ACCESS

or, for an existing assembly:

ALTER ASSEMBLY [MyAssemblyName] WITH PERMISSION_SET = UNSAFE;

 

Other security check stuff

•  exec sp_helprotect

 

SELECT su.name, -- user name with permission
so.name -- procedure name
,*
FROM syspermissions AS sp
INNER JOIN sysobjects AS so
ON sp.id = so.id
INNER JOIN sysusers AS su
ON sp.grantee = su.uid
--WHERE so.name = 'SystemSLS_OpdaterLoenFremskrivningBudget'
WHERE so.name like 'SystemSLS_%'

 

 

Assemblies:

SELECT pr.name, *
FROM sys.server_principals pr
INNER JOIN sys.server_permissions pe
ON pr.principal_id = pe.grantee_principal_id
–WHERE pe.permission_name = ‘EXTERNAL ACCESS ASSEMBLY’

select * from sys.assemblies

 

 

---

---

 

Other again stuff

select * from sys.servers

select * from sys.databases