Making a more reliable and flexible sp_MSforeachdb

Making a more reliable and flexible sp_MSforeachdb

SQL Server Websites

SQL Server Websites

Sending email from SQL Server Integration Services (SSIS)

Sending email from SQL Server Integration Services (SSIS)

Using the Foreach ADO Enumerator in SSIS - CodeProject

Using the Foreach ADO Enumerator in SSIS - CodeProject

Tempdb Configuration Best Practices in SQL Server

Tempdb Configuration Best Practices in SQL Server

Remove SQL Server database from single-user mode

Remove SQL Server database from single-user mode

SQL Server Security Audit Report

USE master
GO
SET nocount ON

-- Get all roles
CREATE TABLE #temp_srvrole
(ServerRole VARCHAR(128), Description VARCHAR(128))
INSERT INTO #temp_srvrole
EXEC sp_helpsrvrole

-- sp_help syslogins
CREATE TABLE #temp_memberrole
(ServerRole VARCHAR(128),
MemberName VARCHAR(265),
MemberSID VARCHAR(300))

DECLARE @ServerRole VARCHAR(128)

DECLARE srv_role CURSOR FAST_FORWARD FOR
SELECT ServerRole FROM #temp_srvrole
OPEN srv_role
FETCH NEXT FROM srv_role INTO @ServerRole

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp_memberrole
EXEC sp_helpsrvrolemember @ServerRole
FETCH NEXT FROM srv_role INTO @ServerRole
END

CLOSE srv_role
DEALLOCATE srv_role

SELECT ServerRole, MemberName FROM #temp_memberrole

-- IF BUILTIN\Administrators is exist and sysadmin
IF EXISTS(SELECT *FROM #temp_memberrole
WHERE MemberName = 'BUILTIN\Administrators'
AND ServerRole = 'sysadmin' )
BEGIN
CREATE TABLE #temp_localadmin (output VARCHAR(8000))
INSERT INTO #temp_localadmin
EXEC xp_cmdshell 'net localgroup administrators'

SELECT output AS local_administrator
FROM #temp_localadmin
WHERE output LIKE '%\%'
DROP TABLE #temp_localadmin
END

DROP TABLE #temp_srvrole
DROP TABLE #temp_memberrole

-- Get individual Logins
SELECT name, 'Individual NT Login' LoginType
FROM syslogins
WHERE isntgroup = 0 AND isntname = 1
UNION
SELECT name, 'Individual SQL Login' LoginType
FROM syslogins
WHERE isntgroup = 0 AND isntname = 0
UNION ALL
-- Get Group logins
SELECT name,'NT Group Login' LoginType
FROM syslogins
WHERE isntgroup = 1


-- get group list
-- EXEC xp_cmdshell 'net group "AnalyticsDev" /domain'
CREATE TABLE #temp_groupadmin
(output VARCHAR(8000))
CREATE TABLE #temp_groupadmin2
(groupName VARCHAR(256), groupMember VARCHAR(1000))
DECLARE @grpname VARCHAR(128)
DECLARE @sqlcmd VARCHAR(1000)

DECLARE grp_role CURSOR FAST_FORWARD FOR
SELECT REPLACE(name,'US\','')
FROM syslogins
WHERE isntgroup = 1 AND name LIKE 'US\%'

OPEN grp_role
FETCH NEXT FROM grp_role INTO @grpname

WHILE @@FETCH_STATUS = 0
BEGIN

SET @sqlcmd = 'net group "' + @grpname + '" /domain'
TRUNCATE TABLE #temp_groupadmin

PRINT @sqlcmd
INSERT INTO #temp_groupadmin
EXEC xp_cmdshell @sqlcmd

SET ROWCOUNT 8
DELETE FROM #temp_groupadmin

SET ROWCOUNT 0

INSERT INTO #temp_groupadmin2
SELECT @grpname, output FROM #temp_groupadmin
WHERE output NOT LIKE ('%The command completed successfully%')

FETCH NEXT FROM grp_role INTO @grpname
END


CLOSE grp_role
DEALLOCATE grp_role

SELECT * FROM #temp_groupadmin2

DROP TABLE #temp_groupadmin
DROP TABLE #temp_groupadmin2



PRINT 'EXEC sp_validatelogins '
PRINT '----------------------------------------------'
EXEC sp_validatelogins
PRINT ''


-- Get all the Database Rols for that specIFic members
CREATE TABLE #temp_rolemember
(DbRole VARCHAR(128),MemberName VARCHAR(128),MemberSID VARCHAR(1000))
CREATE TABLE #temp_rolemember_final
(DbName VARCHAR(100), DbRole VARCHAR(128),MemberName VARCHAR(128))

DECLARE @dbname VARCHAR(128)
DECLARE @sqlcmd2 VARCHAR(1000)

DECLARE grp_role CURSOR FOR
SELECT name FROM sysdatabases
WHERE name NOT IN ('tempdb')
AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'


OPEN grp_role
FETCH NEXT FROM grp_role INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

TRUNCATE TABLE #temp_rolemember
SET @sqlcmd2 = 'EXEC [' + @dbname + ']..sp_helprolemember'

PRINT @sqlcmd2
INSERT INTO #temp_rolemember
EXECUTE(@sqlcmd2)

INSERT INTO #temp_rolemember_final
SELECT @dbname AS DbName, DbRole, MemberName
FROM #temp_rolemember

FETCH NEXT FROM grp_role INTO @dbname
END


CLOSE grp_role
DEALLOCATE grp_role

SELECT * FROM #temp_rolemember_final

DROP TABLE #temp_rolemember
DROP TABLE #temp_rolemember_final

SSIS - Dynamically set Flat File Connection Manager - Sudeep's Domain

SSIS - Dynamically set Flat File Connection Manager - Sudeep's Domain

SQL Server Encryption - Encrypt a database

USE master;
GO
--master key password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '****';
--DROP MASTER KEY
go
--database encryption key
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'Certificate'
--DROP CERTIFICATE MyServerCert
go


USE master
GO
BACKUP CERTIFICATE MyServerCert
TO FILE = 'C:\File.cer'
WITH PRIVATE KEY (FILE = 'C:\Key.pvk' ,
ENCRYPTION BY PASSWORD = '*****' )
GO


USE [cleint]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert
GO
ALTER DATABASE [cleint]
SET ENCRYPTION ON
GO

SELECT db_name(database_id), encryption_state, *
FROM sys.dm_database_encryption_keys with (nolock)


SELECT * FROM sys.symmetric_keys
SELECT * FROM sys.certificates
SELECT * FROM sys.dm_database_encryption_keys

SQL 2008 Server Audit - Server and Database Specification

--Create Audit
CREATE SERVER AUDIT [DDL-Audit]
TO FILE
( FILEPATH = N'E:\Audit\'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = '877f038b-d57c-44d1-8db4-c2c3dc1321a1'
)
GO

--Create Audit specifics Server Level
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification]
FOR SERVER AUDIT [DDL-Audit]
ADD (DATABASE_OBJECT_ACCESS_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
ADD (SERVER_STATE_CHANGE_GROUP),
ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP)
WITH (STATE = ON)
GO

--Create Audit specifics Server Level
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout]
FOR SERVER AUDIT [Login-Logout-Audit]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP)
WITH (STATE = ON)
GO


--Create Audit Specific Database Level
CREATE DATABASE AUDIT SPECIFICATION [DDL_changes_audit]
FOR SERVER AUDIT [DDL-Audit]
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO

--Read from Audit Trace File
select * FROM sys.fn_get_audit_file('E:\Audit\DDL*',default,default)


--Disable Audit and Server Audit Specification
ALTER SERVER AUDIT [DDL-Audit] with (State = OFF)
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout] with (State = OFF)


--Enable Audit and Server Audit Specification
ALTER SERVER AUDIT [DDL-Audit] with (State = ON)
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout] with (State = ON)