120 private links
Script resources for IT professionals
Download resources and applications for Windows 10, Windows 8, Windows 7, Windows Server 2012 R2, Windows Server 2012,Windows Server 2008 R2, Windows Server 2008, SharePoint, System Center, Office and other products. Find resources written in VB Script, PowerShell, SQL, JavaScript or other script languages. New resources are added frequently so check often and see what's new.,
/*
This script will script the role members for all roles on the database.
This is useful for scripting permissions in a development environment before refreshing
development with a copy of production. This will allow us to easily ensure
development permissions are not lost during a prod to dev restoration.
*/
SET NOCOUNT ON;
SELECT [-- SQL STATEMENTS --] FROM (
/*****/
/* HEADER */
/*****/
SELECT 'PRINT ''-----------------------------------'';
PRINT ''Script Copy Roles & Permissions''
PRINT ''''
PRINT ''Database : '+db_name()+'''
PRINT ''''
PRINT GetDate()
PRINT ''-----------------------------------'';
PRINT '''';' AS [-- SQL STATEMENTS --]
, 0 AS [-- RESULT ORDER HOLDER --]
UNION
/*****/
/* DB CONTEXT STATEMENT */
/*****/
SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],
1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],
1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT '' AS [-- SQL STATEMENTS --],
2 AS [-- RESULT ORDER HOLDER --]
UNION
/*****/
/* DB ROLE PERMISSIONS */
/*****/
SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
3 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 'EXEC sp_addrolemember @rolename ='
- SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' +
SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') + ';'
AS [-- SQL STATEMENTS --],
3 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_role_members AS rm
WHERE USER_NAME(rm.member_principal_id) IN (
--get user names on the database
SELECT [name]
FROM sys.database_principals
WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
and [type] IN ('G', 'S', 'U')
-- S = SQL user, U = Windows user, G = Windows group
)
--ORDER BY rm.role_principal_id ASC
UNION
SELECT '' AS [-- SQL STATEMENTS --],
4 AS [-- RESULT ORDER HOLDER --]
UNION
/*****/
/* OBJECT LEVEL PERMISSIONS */
/*****/
SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT CASE
WHEN perm.state <> 'W' THEN perm.state_desc
ELSE 'GRANT'
END
- SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) +
'.' + QUOTENAME(obj.name) --select, execute, etc on specific objects - CASE
WHEN cl.column_id IS NULL THEN SPACE(0)
ELSE '(' + QUOTENAME(cl.name) + ')'
END - SPACE(1) + 'TO' + SPACE(1) +
QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default - CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --]
FROM
sys.database_permissions AS perm
INNER JOIN
sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
--WHERE usr.name = @OldUser
--ORDER BY perm.permission_name ASC, perm.state_desc ASC
UNION
SELECT '' AS [-- SQL STATEMENTS --],
6 AS [-- RESULT ORDER HOLDER --]
UNION
/*****/
/* DB LEVEL PERMISSIONS */
/*****/
SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --]
, 7 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT CASE
WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
ELSE 'GRANT'
END
- SPACE(1) + perm.permission_name --CONNECT, etc
- SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO
- CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --]
, 7 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
--WHERE usr.name = @OldUser
WHERE [perm].[major_id] = 0
AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas
AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
) AS TB
ORDER BY [-- RESULT ORDER HOLDER --]
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
-- specify database backup directory
SET @path = 'E:\Backup\'
---- specify filename format yyyy-mm-ddhh.mm.ss
--SELECT @fileDate = CONVERT(VARCHAR(10), GETDATE(), 120) +''+ REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','.')
-- specify filename format yyyy-mm-ddhh.mm
SELECT @fileDate = CONVERT(VARCHAR(10), GETDATE(), 120) +''+ REPLACE(CONVERT(VARCHAR(5),GETDATE(),108),':','.')
print REPLACE(CONVERT(VARCHAR(5),GETDATE(),108),':','.')
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCHSTATUS = 0
BEGIN
SET @fileName = @path + @name + '' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName;
FETCH NEXT FROM db_cursor INTO @name;
END
CLOSE db_cursor
DEALLOCATE db_cursor
select
'' + char(13) + char(10)
- 'go' + char(13) + char(10)
- 'if ((select count(*) from ' + sys.schemas.name + '.[' + sys.tables.name +'])>0) '+ char(13) + char(10)
- 'begin' + char(13) + char(10)
- ' print ''---------------------'' ' + char(13) + char(10)
- ' print ''' + sys.schemas.name + '.' + sys.tables.name + ''' ' + char(13) + char(10)
- ' delete from ' + sys.schemas.name + '.[' + sys.tables.name +']'+ char(13) + char(10)
- 'end' + char(13) + char(10)
FROM sys.tables INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id
ORDER BY sys.schemas.name, sys.tables.name
--Script created by Vyas
--http://vyaskn.tripod.com
SET NOCOUNT ON
DECLARE @OldUser sysname, @NewUser sysname
SET @OldUser = 'HRUser'
SET @NewUser = 'PersonnelAdmin'
SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'
SELECT '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser) AS '--Comment'
SELECT 'EXEC sp_addrolemember @rolename ='
- SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') AS '--Role Memberships'
FROM sys.database_role_members AS rm
WHERE USER_NAME(rm.member_principal_id) = @OldUser
ORDER BY rm.role_principal_id ASC
SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
- SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
- CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
- SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
- CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN
sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
WHERE usr.name = @OldUser
ORDER BY perm.permission_name ASC, perm.state_desc ASC
SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
- SPACE(1) + perm.permission_name + SPACE(1)
- SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
- CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'
FROM sys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE usr.name = @OldUser
AND perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC
Most SQL Server administrative tasks are programmable thanks to a set of objects known as SQL-DMO. This article introduces the concepts of programming DMO, and steps you through some basic, and commonly requested examples.
Const ForWriting = 2
Dim job
Dim oFSO
Dim oFSOWrite
Dim oSQLServer
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.LoginSecure = True
oSQLServer.Connect "(local)"
Set oFSOWrite = oFSO.OpenTextFile("c:\sqljobs.sql", ForWriting, True)
For Each job in oSQLServer.JobServer.Jobs
oFSOWrite.WriteLine job.script
Next
oFSOWrite.Close
Set oFSO = Nothing
We all like the Generate Script feature of Enterprise Manager and like to use it programmatically. One of the most important uses would be backing up scripts periodically using SQL Server Scheduler.
Script.NET or S# is a scripting language for .NET applications which allows to customize behavior of your .NET applications, interact with native .NET objects, types and assemblies. The hosting application itself could change the default behavior of script run-time by an xml configuration, replacing binding behavior to methods, properties, constructors of native .NET types; name, function and type resolving and much more. Business description as well as typical use-cases of S# are covered in the product whitepaper.
NScript is a tool like WScript except that it allows for scripts to be written in .NET languages like C# and VB.NET