SQL Server – Powershell Active Directory search

 Working with Powershell and Active Directory simplifies some complex tasks for the DBA.

Active Directory is LDAP compliant. This means the RFC 1779 and RFC 2247 standards are met.

This example lists all employees on an LDAP path. This method requires knowledge of the LDAP path .

 Common Name (cn)

Organizational Unit (ou)

Domain Component (dc)

 

$group = [ADSI] "LDAP://cn=MYORGAllEmployees,ou=MYORG,dc=south,dc=syborg,dc=net" 
foreach ($member in $group.member) 
{ 
$member 
} 

 

See Also

Powershell sql server security audit

SQL Traces and c2 auditing are different

Sometimes end users interchange the terms : C2 auditing and Traces.
They server different purposes.

c2 auditing records all attempts (successfull and failed) on objects and statements. Typically a SQL Server DBA will use a c2 audit to monitor security compliance .

A trace will will monitor an event or a series of events. Events can be any source producing a trace events. Examples are deadlocks and DTC transactions, SQL Server Backups.Read BOL for a comprehensive list of events

To view if c2 auditing is turned on use:

--To view if c2 auditing is turned on use:

EXEC sp_configure 'c2 audit mode'


--To view if traces including user defined ones are running on SQL server 2005 use:

SELECT * FROM sys.fn_trace_getinfo(0) ;

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Enable XA transactions for SQL Server

An XA transaction is a  global transaction usually covering multiple resources.

Use  the  Microsoft SQL Server JDBC Driver to support the   Java Platform based distributed transactions

Steps to enable XA transactions

1)  Enable MSDTC

On Windows 2003 > Start > Run > “dcomconfg” > Expand Component Services > Expand Computers > Right -click  My Computer > Click Properties > Click MSDTC tab > Click Security Configuration > Check the “Enable XA Transactions”

Use MS DTC and sys.dm_tran_active_transactions  to monitor the state of the MSDTC transactions

2) Install  SQLJDBC_XA.dll 

Prior to running this script  copy the extended stored procedure dll SQLJDBC_XA.dll  to the target SQL Server's Binn folder.  Restart the SQL Server Instance

3) Create a role  and Attach the role to relevant users

Permissions to the distributed transaction support procedures for the Microsoft SQL Server 2005  JDBC Driver are granted through the SQL Server role [SqlJDBCXAUser].  Maintain a secure default configuration, no user is granted access to this role by default. Ensure the actions you execute are compatible with your SQL Server Security Policy

 

use master
go

-- Drop any existing procedure definitions.
exec sp_dropextendedproc 'xp_sqljdbc_xa_init' 
exec sp_dropextendedproc 'xp_sqljdbc_xa_start'
exec sp_dropextendedproc 'xp_sqljdbc_xa_end'
exec sp_dropextendedproc 'xp_sqljdbc_xa_prepare'
exec sp_dropextendedproc 'xp_sqljdbc_xa_commit'
exec sp_dropextendedproc 'xp_sqljdbc_xa_rollback'
exec sp_dropextendedproc 'xp_sqljdbc_xa_forget'
exec sp_dropextendedproc 'xp_sqljdbc_xa_recover'
exec sp_dropextendedproc 'xp_sqljdbc_xa_rollback_ex'
exec sp_dropextendedproc 'xp_sqljdbc_xa_forget_ex'
exec sp_dropextendedproc 'xp_sqljdbc_xa_prepare_ex'
go

-- Install the procedures.
exec sp_addextendedproc 'xp_sqljdbc_xa_init', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_start', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_end', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_prepare', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_commit', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_rollback', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_forget', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_recover', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_rollback_ex', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_forget_ex', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_prepare_ex', 'SQLJDBC_XA.dll'
go
use master 
GO
sp_addrole [SqlJDBCXAUser]
go

-- Grant privileges to [SqlJDBCXAUser] role to the extended stored procedures.
grant execute on xp_sqljdbc_xa_init to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_start to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_end to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_prepare to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_commit to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_rollback to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_recover to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_forget to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_rollback_ex to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_forget_ex to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_prepare_ex to [SqlJDBCXAUser]
go

 

Author: Jack Vamvas (http://www.sqlserver-dba.com)

ALTER INDEX permissions

Developers like to have some control over the development process. An example is index building.  Asking a DBA repeatedly for changes  to a Development Database  might not be the best use of time – for either parties.

For example , a developer may want to work over the weekend to finish a Data Import and Export  process

---To execute ALTER INDEX, at a minimum, ALTER permission on the table or view is required.
GRANT ALTER on My Table  to MY_SQL_USER

---To revoke the ALTER INDEX permissions.
GO
DENY ALTER on My Table  to MY_SQL_USER

Author: Jack Vamvas (http://www.sqlserver-dba.com)

ALTER AUTHORIZATION sql to change owner of a database

ALTER AUTHORIZATION changes the ownership of  entities .  Server level entity ownership can be changed to server- level principals. Database level ownership can be changed to database level principals.

ALTER AUTHORIZATION can change database ownership.  This replaces sp_changedbowner.  Alter any security management scripts using sp_changedbowner  - such as Attach database without log file and rename database

According to SQL Server BOL

“This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER AUTHORIZATION instead”

An example of ALTER AUTHORIZATION : a database  without an owner , may require an owner to be assigned. The login used to create the database or be assigned the database ownership  has been dropped. The database does not have an owner.

Using ALTER AUTHORIZATION assigns an owner.

 Example:

 ALTER AUTHORIZATION ON DATABASE::MyDatabase TO MyLoginUser;

 

There are special conditions around the usage of ALTER AUTHORIZATION . Read SQL Server BOL for details.

 

Author: Jack Vamvas (http://www.sqlserver-dba.com)