[EN] Sunday Market–PowerShell Scripts by Idera

HeroShot_PowershellScriptsIt Sunday again. And some shops are closed. If Yes, it’s time to get next useful tool for free. it’s my fifth episode in non-frequently series: Sunday Market. This time we will look for Idera again. This time we will not install anything. Just download and run. For free download You must:
>> go to Idera PowerShell Scripts site HERE
>> fill registration data
>> checking Your mail box for download link

Easy. As always. Next – when You downloaded nat too large (82 MB) file – we can look inside. This special package conains compilation of scripts for: Sharepoint Server, SQL Server (2005 & 2008), Exchange Server, Active Directory and just for PowerShell Plus Trial Installators (for 32- and 64-bits). Lets look for all parts:

\IderaPowerShellScripts\Idera_ADPowerShellScripts\

Add-IADGroupMember.ps1 / Disable-IADObject.ps1 / Disable-UserCannotChangePassword.ps1 / Enable-IADObject.ps1 / Enable-UserCannotChangePassword.ps1 / Get-IADAltRecipient.ps1 / Get-IADComputer.ps1 / Get-IADCurrentDomain.ps1 / Get-IADCurrentForest.ps1 / Get-IADDomainController.ps1 / Get-IADDomainController2.ps1 / Get-IADDomainPasswordPolicy.ps1 / Get-IADEmptyGroup.ps1
/ Get-IADExchangeAdmin.ps1 / Get-IADFSMORoleHolder.ps1 / Get-IADGroup.ps1 / Get-IADGroupMember.ps1 / Get-IADGroupMembership.ps1 / Get-IADObjectBySID.ps1 / Get-IADObjectSID.ps1 / Get-IADSite.ps1 / Get-IADSubnet.ps1 / Get-IADTombstoneComputer.ps1 / Get-IADTombstoneObject.ps1 / Get-IADTombstoneUser.ps1 / Get-IADUser.ps1 / Move-IADObject.ps1 / New-IADGroup.ps1 / New-IADPassword.ps1 / New-IADUser.ps1 / Remove-IADGroupMember.ps1 / Remove-IADObject.ps1 / Set-IADUser.ps1

\Idera_ExchangeScripts\
Disable-IEXUser.ps1 / Get-IEXAvailableSpace.ps1 / Get-IEXClusterNode.ps1 / Get-IEXCurrentRole.ps1 / Get-IEXDatabaseQuotaStatistics.ps1 / Get-IEXDatabaseSize.ps1 / Get-IEXDatabaseStatistics.ps1 / Get-IEXEmailAddressOwner.ps1 / Get-IEXEmailAddressReport.ps1 / Get-IEXForwardMailbox.ps1 / Get-IEXInactiveMailbox.ps1 / Get-IEXLastSendAndReceivedDate.ps1 / Get-IEXMailboxAddressCount.ps1 / Get-IEXMailboxCountReport.ps1 / Get-IEXMailboxFolderStatistics.ps1 / Get-IEXMailboxStatistics.ps1 / Get-IEXSendAsPermission.ps1 / Get-IEXTip.ps1 / New-IEXAddressList.ps1 / New-IEXMailboxFromCsv.ps1 / New-IEXTestMailbox.ps1 / Purge-IEXMailbox.ps1 / Remove-IEXDisabledUser.ps1 / Set-IEXEmailAddress.ps1 / Test-IEXDistinguishedName.ps1 / Test-IEXEmailAddress.ps1 / Test-IEXEmailAddress2.ps1

\IderaPowerShellScripts\Idera_SharePointScripts\
Add-SPList.ps1 / Add-SPSite.ps1 / Add-SPUser.ps1 / CheckIn-SPDocument.ps1 / CheckOut-SPDocument.ps1 / Display-SPItemInformation.ps1 / Display-SPList.ps1 / Display-SPWebPart.ps1 / Export-SPSite.ps1 / Get-SPView.ps1 / Get-SPWeb.ps1 / Import-SPSite.ps1 / Remove-SPList.ps1 / Remove-SPSite.ps1 / Set-SPItem.ps1 / ++ Readme.doc & Readme.pdf

\IderaPowerShellScripts\Idera_SQLServerScripts\
for SQL Server 2005
Backup-SSAS.ps1 / CheckDB-MSSQL-UsingADO.ps1 / Connect-MSSQL-IPSQLAuth.ps1 / Connect-MSSQL-IPWindowsAuth.ps1 / CreateDB-MSSQL-UsingSMO.ps1 / Create-MSSQLJob-UsingSMO.ps1 / Create-MSSQL-TestDb.ps1 / CreateTable-MSSQL-UsingSMO.ps1 / Get-MSSQL-DB-UsingADO.ps1 / Get-MSSQL-DB-UsingSMO.ps1 / Get-MSSQL-MaxMemory.ps1 / Get-MSSQL-Port-UsingDMO.ps1 / Get-MSSQL-Port-UsingWMI.ps1 / Get-MSSQL-ServerAttrib-Csv.ps1 / Get-MSSQL-ServerAttrib-Html.ps1 / Get-MSSQL-ServerRegisterations.ps1 / Get-MSSQL-Views-Csv.ps1 / Insert-MSSQL-SampleData-Csv.ps1 / List-MSSQL-FailedJobs.ps1List-MSSQL-PropertyInfo.ps1Load-MSSQL-SMO.ps1Start-SQLServerService.ps1Stop-SQLServerService.ps1 / ++SampleData.csv / ++Readme.doc & Readme.pdf

for SQL Server 2008
Backup-SSAS.ps1 / CheckDB-MSSQL-UsingADO.ps1 / Connect-MSSQL-IPSQLAuth.ps1 / Connect-MSSQL-IPWindowsAuth.ps1 / CreateDB-MSSQL-UsingSMO.ps1 / Create-MSSQLJob-UsingSMO.ps1 / Create-MSSQL-TestDb.ps1 / CreateTable-MSSQL-UsingSMO.ps1 / Get-MSSQL-DB-UsingADO.ps1 / Get-MSSQL-DB-UsingSMO.ps1 / Get-MSSQL-MaxMemory.ps1 / Get-MSSQL-Port-UsingDMO.ps1 / Get-MSSQL-Port-UsingWMI.ps1 / Get-MSSQL-ServerAttrib-Csv.ps1 / Get-MSSQL-ServerAttrib-Html.ps1 / Get-MSSQL-ServerRegisterations.ps1 / Get-MSSQL-Views-Csv.ps1 / Insert-MSSQL-SampleData-Csv.ps1 / List-MSSQL-FailedJobs.ps1 / List-MSSQL-PropertyInfo.ps1 / Load-MSSQL-SMO.ps1 / Start-SQLServerService.ps1 / Stop-SQLServerService.ps1 / ++SampleData.csv / ++Readme.doc & Readme.pdf

Interesting listing, right? Let’s run few examples below:

*** CHECKDB ***

## ======================================================

## Title       : CheckDB-MSSQL-UsingADO

## Description : Run a DBCC against specified server instance and database

## Author      : Idera

## Date        : 9/1/2008

## Input       : -serverInstance <server\instance>

##               -dbName <database name>

##               -verbose

##               -debug             

## Output      :

## Usage                                              : PS> .\CheckDB-MSSQL-UsingADO -serverInstance local -dbName master -verbose -debug

## Notes                                               : Adapted from Jakob Bindslet script

## Tag                                   : SQL Server, ADO, CheckDB

## Change log  :

## ======================================================

 

param

(

   [string]$serverInstance = “.”,

                [string]$dbName = “AdventureWorks”,

                [switch]$verbose,

                [switch]$debug

)

 

function main()

{

                if ($verbose) {$VerbosePreference = “Continue”}

                if ($debug) {$DebugPreference = “Continue”}

                CheckDBMSSQLUsingADO $serverInstance $dbName

}

 

function CheckDB-MSSQL-UsingADO($serverInstance, $dbName)

{

                trap [Exception]

                {

                               write-error $(“TRAPPED: “ + $_.Exception.Message);

                               continue;

                }

 

                $cn = new-object system.data.SqlClient.SqlConnection( `

                               “Data Source=$serverInstance;Integrated Security=SSPI;Initial Catalog=$dbName”);

                $ds = new-object System.Data.DataSet “dsCheckDB”

                $query = “DBCC CHECKDB($dbName) WITH TABLERESULTS”

                $da = new-object “System.Data.SqlClient.SqlDataAdapter” ($query, $cn)

                $da.Fill($ds)

 

                $dtCheckDB = new-object “System.Data.DataTable” “dsCheckDB”

                $dtCheckDB = $ds.Tables[0]

                $dtCheckDB | Format-Table -autosize `

                               -property Error, Level, State, MessageText, `

                                                                                              RepairLevel, Status, DbId, ObjectId, `

                                                                                              IndexId, PartitionId, AllocUnitId, File, `

                                                                                              Page, Slot, RefFile, RefPage, RefSlot, Allocation

}

 

main

*** MAXMEMORY ***

## ======================================================

## Title       : Get-MSSQL-MaxMemory

## Description : Get max memory property from SQL Server

## Author      : Idera

## Date        : 9/1/2008

## Input       : -serverInstance           <server\instance>

##                                                            -verbose

##                                                            -debug

## Output      : Integer in MB,

##                                                                          -1 if memory is unlimited,

##                                                                          -2 if no connection to the desired DB can be obtained

## Usage                                              : PS> .\Get-MSSQL-MaxMemory -serverInstance MyServer -verbose -debug

## Notes                                               : Adapted from Jakob Bindslet script

## Tag                                   : SQL Server, ADO, Configuration, Memory

## Change log  :

## ======================================================

 

param

(

                [string]$serverInstance = “local”,

                [switch]$verbose,

                [switch]$debug

)

 

function main()

{

                if ($verbose) {$VerbosePreference = “Continue”}

                if ($debug) {$DebugPreference = “Continue”}

                GetMSSQLMaxMemory $serverInstance

}

 

function Get-MSSQL-MaxMemory($serverInstance)

{

                trap [Exception]

                {

                               write-error $(“TRAPPED: “ + $_.Exception.Message);

                               continue;

                }

 

                #initialize ADO connection parameters

                Write-Debug “Initializing connection parameters…”

                $adoOpenStatic = 3

                $adoLockOptimistic = 3

                $timeout = 3

 

                #get memory configuration setting from sysconfigures system table

                # TIP: using ADO to run a query

                Write-Debug “Connecting to server: $serverInstance”

                $adoConnection = New-Object -comobject ADODB.Connection

                $adoRecordset = New-Object -comobject ADODB.Recordset

                $query = “SELECT value FROM [master].[dbo].[sysconfigures] WHERE config = 1544”

                $adoConnection.Set_ConnectionString(“Provider=SQLOLEDB; Data Source=” + $srv + “; Initial Catalog=master; Integrated Security=SSPI”)

                $adoConnection.Set_ConnectionTimeout($timeout)

                $adoConnection.Open()

 

                if ($adoConnection.State -eq “1”)

                {

                               Write-Debug “Connection succeeded…”

                $adoRecordset.Open($query, $adoConnection, $adoOpenStatic, $adoLockOptimistic)

                $adoRecordset.MoveFirst()

                $maxMemory = ($adoRecordset.Fields.Item(“value”).Value)

                if ($maxMemory -eq 2147483647)

                {

                                               Write-Verbose “Max memory is set to unlimited”

                      $maxMemory = 1

                }

                $adoRecordset.Close()

                $adoConnection.Close()

                }

                else

                {

                               Write-Debug “Connection failed…”

                   $maxMemory = 2

                }

                Write-Debug “Max memory is set to $maxMemory”

                Write-Output $maxMemory

}

 

main

*** CREATE TABLE ***

# ======================================================

## Title       : CreateTable-MSSQL-UsingSMO

## Description : Create a new table using SMO

## Author      : Idera

## Date        : 1/28/2009

## Input       : -serverInstance <server\instance>

##                                                            -dbName <database name>

##                                                            -schemaName <schema name>

##                                                            -tblName <table name>

##                                                            -verbose

##                                                            -debug

## Output      : Create a demo database and table

## Usage                                              : PS> .\CreateTable-MSSQL-UsingSMO -serverInstance MyServer -dbName SMOTestDB

##                                                                                                                                                        -schemaName SMOSchema -tblName SMOTable -verbose -debug

## Notes                                               : Adapted from Allen White script

## Tag                                   : SMO, SQL Server, Table

## Change log  : Revised SMO Assemblies

## ======================================================

 

param

(

                [string]$serverInstance = “(local)”,

                [string]$dbName = “SMOTestDB”,

                [string]$schemaName = “SMOSchema”,

                [string]$tblName = “SMOTable”,

                [switch]$verbose,

                [switch]$debug

)

 

function main()

{

                if ($verbose) {$VerbosePreference = “Continue”}

                if ($debug) {$DebugPreference = “Continue”}

                CreateTableMSSQLUsingSMO $serverInstance $dbName $schemaName $tblName

}

 

function CreateTable-MSSQL-UsingSMO($serverInstance, $dbName, $schemaName, $tblName)

{

                trap [Exception]

                {

                               write-error $(“TRAPPED: “ + $_.Exception.Message);

                               continue;

                }

               

                # Load SMO assemblies

                [void][reflection.assembly]::LoadWithPartialName( “Microsoft.SqlServer.Management.Common” );

                [void][reflection.assembly]::LoadWithPartialName( “Microsoft.SqlServer.SmoEnum” );

                [void][reflection.assembly]::LoadWithPartialName( “Microsoft.SqlServer.Smo” );

                [void][reflection.assembly]::LoadWithPartialName( “Microsoft.SqlServer.SmoExtended “ );

               

                # Instantiate a server object for the default instance

                $namedInstance = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) ($serverInstance)

               

                # Disable connection pooling

                $namedInstance.ConnectionContext.Set_NonPooledConnection($TRUE)

               

                # Explicitly connect because connection pooling is disabled

                $namedInstance.ConnectionContext.Connect()

 

                cls

               

                # Connect to the server with Windows Authentication and drop database if exist

                if ($namedInstance.Databases[$dbName] -ne $null)

                {

                               Write-Debug “The test database already exists on $namedInstance”

                               Write-Debug “Deleting it now…”

                               $namedInstance.Databases[$dbName].drop()

                }

 

                # Instantiate a database object

                Write-Debug “Creating database: $dbName”

                $database = newobject(“Microsoft.SqlServer.Management.Smo.Database”) ($namedInstance, $dbName)

               

                # Create the new database on the server

                $database.Create()

               

                # Instantiate a schema object

                Write-Debug “Creating schema: $SchemaName”

                $schema = newobject(“Microsoft.SqlServer.Management.Smo.Schema”) ($database, $schemaName)

               

                # Create the new schema on the server

                $schema.Create()

               

                # Instantiate a table object

                Write-Debug “Creating table: $TblName”

                $table = newobject(“Microsoft.SqlServer.Management.Smo.Table”) ($Database, $TblName)

               

                # Add Field1 column

                $colField1 = NewObject(“Microsoft.SqlServer.Management.Smo.Column”) ($table, “Field1”)

                # TIP: setting an object type from a NetClassStaticMethod

                $colField1.DataType = [Microsoft.SqlServer.Management.Smo.Datatype]::Int

                $table.Columns.Add($colField1)

               

                # Add Field2 column

                $colField2 = NewObject(“Microsoft.SqlServer.Management.Smo.Column”) ($table, “Field2”)

                $colField2.DataType =  [Microsoft.SqlServer.Management.Smo.Datatype]::NVarchar(25)

                $table.Columns.Add($colField2)

               

                # Add Field3 column

                $colField3 = NewObject(“Microsoft.SqlServer.Management.Smo.Column”) ($table, “Field3”)

                $colField3.DataType = [Microsoft.SqlServer.Management.Smo.Datatype]::NVarchar(50)

                $Table.Columns.Add($colField3)

               

                # Set the schema property of the table.

                $table.Schema = $schemaName

               

                # Create the table on the server

                $table.Create()

               

                Write-Host “Table: $dbName.$schemaName.$tblName created”

               

                # Explicitly disconnect because connection pooling is disabled

                Write-Debug “Disconnecting…”

                $namedInstance.ConnectionContext.Disconnect()

}

 

main

 

[EN] Sunday Market–SQL Permissions by Idera (part two)

CaptureSQLPermissionThe second part of my previous post is still about SQL Permission – free tool from Idera. In previous I wrote about installation post. In this post I’ll showhow look script from one of my database: by default: instance, all databases, all bermissions, all logins, all users. It’s small database (SQL Server 2012 (Denali) CTP3 Express Edition) but the result of script is very large: over seventy pages in MS Word document. Yay! I loose weight (freed some space by deleting sixty pages) of this file by deleting all part of the code begins with:

USE [msdb]

GO

Grant EXECUTE ON [dbo].[xxx] TO [yyy] 

GO

 

Hope it will help to reading quick and understanding this script. Full version of the script You can download from my SlideShare profile (HERE). OK, let’s go to the script, how it looks?

Part.1 | Creating special permissions with login MS_PolicyEventProcessingLogin, creating and executing query

/*********** Permissions Script for WRON023\KEYKO Login

##MS_PolicyEventProcessingLogin## Generated 2011-10-16 16:22:45 ***************/

 

 

–Scripting Server Login (password is null) and Default Database

/* For security reasons the login is created disabled and with a random password. */

IF NOT EXISTS (SELECT * FROM sys.server_principals

WHERE name = N’##MS_PolicyEventProcessingLogin##’)

BEGIN

CREATE LOGIN [##MS_PolicyEventProcessingLogin##]

WITH PASSWORD=N’îK”îΓN_g•Â_!é@ôz«7_§Oæö½_VˬU#4′,

DEFAULT_DATABASE=[master],

DEFAULT_LANGUAGE=[us_english],

CHECK_EXPIRATION=OFF,  CHECK_POLICY=ON

ALTER LOGIN [##MS_PolicyEventProcessingLogin##] DISABLE

END

 

 

–Scripting Server Roles

USE [master]

GO

 

–Grant Database Access

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’##MS_PolicyEventProcessingLogin##’)

CREATE USER [##MS_PolicyEventProcessingLogin##]

FOR LOGIN [##MS_PolicyEventProcessingLogin##]

WITH DEFAULT_SCHEMA=[dbo]

 

–Scripting Object and Statement Permissions

USE [master]

GO

Grant CONNECT ON Database::[master] TO [##MS_PolicyEventProcessingLogin##] 

GO

 

USE [msdb]

GO

 

 

–Grant Database Access

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’##MS_PolicyEventProcessingLogin##’)

CREATE USER [##MS_PolicyEventProcessingLogin##]

FOR LOGIN [##MS_PolicyEventProcessingLogin##]

WITH DEFAULT_SCHEMA=[dbo]

 

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’PolicyAdministratorRole’ AND type = ‘R’)

CREATE ROLE [PolicyAdministratorRole] AUTHORIZATION [dbo]

 

 

–Scripting Database Role Members

USE [msdb]

GO

 

exec sp_addrolemember N’PolicyAdministratorRole’, N’##MS_PolicyEventProcessingLogin##’

GO

 

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’SQLAgentOperatorRole’ AND type = ‘R’)

CREATE ROLE [SQLAgentOperatorRole] AUTHORIZATION [dbo]

 

USE [msdb]

GO

Grant EXECUTE ON [dbo].[sp_enum_login_for_proxy] TO [SQLAgentOperatorRole] 

GO

 

exec sp_addrolemember N’SQLAgentOperatorRole’, N’##MS_PolicyEventProcessingLogin##’

GO

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’SQLAgentReaderRole’ AND type = ‘R’)

CREATE ROLE [SQLAgentReaderRole] AUTHORIZATION [dbo]

 

exec sp_addrolemember N’SQLAgentReaderRole’, N’##MS_PolicyEventProcessingLogin##’

GO

 

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’SQLAgentUserRole’ AND type = ‘R’)

CREATE ROLE [SQLAgentUserRole] AUTHORIZATION [dbo]

 

exec sp_addrolemember N’SQLAgentUserRole’, N’##MS_PolicyEventProcessingLogin##’

GO

Part.2 | Creating special permissions with login MS_PolicyTsqlExecutionLogin: creating and executing query

 

/*********** Permissions Script for WRON023\KEYKO

Login ##MS_PolicyTsqlExecutionLogin## Generated 2011-10-16 16:22:51 ***************/

 

 

–Scripting Server Login (password is null) and Default Database

/* For security reasons the login is created disabled and with a random password. */

IF NOT EXISTS (SELECT * FROM sys.server_principals

WHERE name = N’##MS_PolicyTsqlExecutionLogin##’)

BEGIN

CREATE LOGIN [##MS_PolicyTsqlExecutionLogin##]

WITH PASSWORD=N’_á*³*óÎõÁ·¹%"_G) š¹`&]ç(æØ]þX*0_’,

DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],

CHECK_EXPIRATION=OFF,  CHECK_POLICY=ON

ALTER LOGIN [##MS_PolicyTsqlExecutionLogin##] DISABLE

END

 

 

–Scripting Server Roles

USE [msdb]

GO

 

 

–Grant Database Access

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’##MS_PolicyTsqlExecutionLogin##’)

CREATE USER [##MS_PolicyTsqlExecutionLogin##]

FOR LOGIN [##MS_PolicyTsqlExecutionLogin##]

WITH DEFAULT_SCHEMA=[dbo]

 

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’PolicyAdministratorRole’ AND type = ‘R’)

CREATE ROLE [PolicyAdministratorRole] AUTHORIZATION [dbo]

 

–Scripting Database Role Members

USE [msdb]

GO

 

exec sp_addrolemember N’PolicyAdministratorRole’, N’##MS_PolicyTsqlExecutionLogin##’

GO

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’SQLAgentOperatorRole’ AND type = ‘R’)

CREATE ROLE [SQLAgentOperatorRole] AUTHORIZATION [dbo]

 

exec sp_addrolemember N’SQLAgentOperatorRole’, N’##MS_PolicyTsqlExecutionLogin##’

GO

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’SQLAgentReaderRole’ AND type = ‘R’)

CREATE ROLE [SQLAgentReaderRole] AUTHORIZATION [dbo]

 

exec sp_addrolemember N’SQLAgentReaderRole’, N’##MS_PolicyTsqlExecutionLogin##’

GO

 

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’SQLAgentUserRole’ AND type = ‘R’)

CREATE ROLE [SQLAgentUserRole] AUTHORIZATION [dbo]

 

 

exec sp_addrolemember N’SQLAgentUserRole’, N’##MS_PolicyTsqlExecutionLogin##’

GO

 

–Scripting Object and Statement Permissions

USE [msdb]

GO

Grant CONNECT ON Database::[msdb] TO [##MS_PolicyTsqlExecutionLogin##] 

GO

 

Part.3 | Creating special permissions with builtin logins Users / SYSTEM / NT AUTHORITY / NT SERVICE / sa / SQLSRVReportingServices / SQLSRVAdmin / “current user”: creating and executing query

/*********** Permissions Script for WRON023\KEYKO

Login BUILTIN\Users Generated 2011-10-16 16:22:53 ***************/

 

–Scripting Server Login (password is null) and Default Database

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N’BUILTIN\Users’)

BEGIN

CREATE LOGIN [BUILTIN\Users] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

END

 

 

–Scripting Server Roles

/*********** Permissions Script for WRON023\KEYKO

Login NT AUTHORITY\SYSTEM Generated 2011-10-16 16:22:55 ***************/

 

–Scripting Server Login (password is null) and Default Database

 

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N’NT AUTHORITY\SYSTEM’)

BEGIN

CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

END

 

–Scripting Server Roles

 

exec sp_addsrvrolemember N’NT AUTHORITY\SYSTEM’, N’sysadmin’

GO

 

 

/*********** Permissions Script for WRON023\KEYKO

Login NT SERVICE\MSSQL$KEYKO Generated 2011-10-16 16:22:57 ***************/

 

 

–Scripting Server Login (password is null) and Default Database

 

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N’NT SERVICE\MSSQL$KEYKO’)

BEGIN

CREATE LOGIN [NT SERVICE\MSSQL$KEYKO] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

END

 

–Scripting Server Roles

 

exec sp_addsrvrolemember N’NT SERVICE\MSSQL$KEYKO’, N’sysadmin’

GO

 

/*********** Permissions Script for WRON023\KEYKO

Login sa Generated 2011-10-16 16:22:58 ***************/

 

–Scripting Server Login (password is null) and Default Database

/* For security reasons the login is created disabled and with a random password. */

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N’sa’)

BEGIN

CREATE LOGIN [sa] WITH PASSWORD=N’¶„áqùm‚G·kƒj_ÂÈò)[¤ã2

ÉY_vY

%¹’, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF,  CHECK_POLICY=ON

EXEC sys.sp_addsrvrolemember @loginame = N’sa’, @rolename = N’sysadmin’

ALTER LOGIN [sa] DISABLE

END

 

–Scripting Server Roles

exec sp_addsrvrolemember N’sa’, N’sysadmin’

GO

 

/*********** Permissions Script for WRON023\KEYKO

Login WRON023\KoprowskiT Generated 2011-10-16 16:23:00 ***************/

 

–Scripting Server Login (password is null) and Default Database

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N’WRON023\KoprowskiT’)

BEGIN

CREATE LOGIN [WRON023\KoprowskiT] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

END

 

–Scripting Server Roles

 

exec sp_addsrvrolemember N’WRON023\KoprowskiT’, N’sysadmin’

GO

 

/*********** Permissions Script for WRON023\KEYKO

Login WRON023\SQLSRVAdmin Generated 2011-10-16 16:23:02 ***************/

 

–Scripting Server Login (password is null) and Default Database

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N’WRON023\SQLSRVAdmin’)

BEGIN

CREATE LOGIN [WRON023\SQLSRVAdmin] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

END

 

–Scripting Server Roles

 

exec sp_addsrvrolemember N’WRON023\SQLSRVAdmin’, N’sysadmin’

GO

 

/*********** Permissions Script for WRON023\KEYKO

Login WRON023\SQLSRVReporting Generated 2011-10-16 16:23:04 ***************/

 

–Scripting Server Login (password is null) and Default Database

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N’WRON023\SQLSRVReporting’)

BEGIN

CREATE LOGIN [WRON023\SQLSRVReporting] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

END

 

–Scripting Server Roles

 

USE [master]

GO

 

–Grant Database Access

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’WRON023\SQLSRVReporting’)

CREATE USER [WRON023\SQLSRVReporting]

FOR LOGIN [WRON023\SQLSRVReporting]

WITH DEFAULT_SCHEMA=[WRON023\SQLSRVReporting]

 

–Create Database Role

USE [master]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’RSExecRole’ AND type = ‘R’)

CREATE ROLE [RSExecRole] AUTHORIZATION [dbo]

 

 

–Scripting Database Role Members

USE [master]

GO

 

exec sp_addrolemember N’RSExecRole’, N’WRON023\SQLSRVReporting’

GO

 

 

–Scripting Object and Statement Permissions

USE [master]

GO

Grant CONNECT ON Database::[master] TO [WRON023\SQLSRVReporting] 

GO

 

USE [msdb]

GO

 

–Grant Database Access

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’WRON023\SQLSRVReporting’)

CREATE USER [WRON023\SQLSRVReporting]

FOR LOGIN [WRON023\SQLSRVReporting]

WITH DEFAULT_SCHEMA=[WRON023\SQLSRVReporting]

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’RSExecRole’ AND type = ‘R’)

CREATE ROLE [RSExecRole] AUTHORIZATION [dbo]

  

 

–Scripting Database Role Members

USE [msdb]

GO

 

exec sp_addrolemember N’RSExecRole’, N’WRON023\SQLSRVReporting’

GO

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’SQLAgentOperatorRole’ AND type = ‘R’)

CREATE ROLE [SQLAgentOperatorRole] AUTHORIZATION [dbo]

 

exec sp_addrolemember N’SQLAgentOperatorRole’, N’WRON023\SQLSRVReporting’

GO

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’SQLAgentReaderRole’ AND type = ‘R’)

CREATE ROLE [SQLAgentReaderRole] AUTHORIZATION [dbo]

 

exec sp_addrolemember N’SQLAgentReaderRole’, N’WRON023\SQLSRVReporting’

GO

 

–Create Database Role

USE [msdb]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’SQLAgentUserRole’ AND type = ‘R’)

CREATE ROLE [SQLAgentUserRole] AUTHORIZATION [dbo]

 

 

exec sp_addrolemember N’SQLAgentUserRole’, N’WRON023\SQLSRVReporting’

GO

 

–Scripting Object and Statement Permissions

USE [msdb]

GO

Grant CONNECT ON Database::[msdb] TO [WRON023\SQLSRVReporting] 

GO

 

USE [ReportServer$KEYKO]

GO

  

–Grant Database Access

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’WRON023\SQLSRVReporting’)

CREATE USER [WRON023\SQLSRVReporting]

FOR LOGIN [WRON023\SQLSRVReporting]

WITH DEFAULT_SCHEMA=[WRON023\SQLSRVReporting]

 

–Scripting Database Role Members

USE [ReportServer$KEYKO]

GO

 

exec sp_addrolemember N’db_owner’, N’WRON023\SQLSRVReporting’

GO

 

–Create Database Role

USE [ReportServer$KEYKO]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’RSExecRole’ AND type = ‘R’)

CREATE ROLE [RSExecRole] AUTHORIZATION [dbo]

 

exec sp_addrolemember N’RSExecRole’, N’WRON023\SQLSRVReporting’

GO

 

 

–Scripting Object and Statement Permissions

USE [ReportServer$KEYKO]

GO

Grant CONNECT ON Database::[ReportServer$KEYKO] TO [WRON023\SQLSRVReporting] 

GO

 

USE [ReportServer$KEYKOTempDB]

GO

 

 

–Grant Database Access

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’WRON023\SQLSRVReporting’)

CREATE USER [WRON023\SQLSRVReporting]

FOR LOGIN [WRON023\SQLSRVReporting]

WITH DEFAULT_SCHEMA=[WRON023\SQLSRVReporting]

 

 

–Scripting Database Role Members

USE [ReportServer$KEYKOTempDB]

GO

 

exec sp_addrolemember N’db_owner’, N’WRON023\SQLSRVReporting’

GO

 

–Create Database Role

USE [ReportServer$KEYKOTempDB]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals

WHERE name = N’RSExecRole’ AND type = ‘R’)

CREATE ROLE [RSExecRole] AUTHORIZATION [dbo]

 

exec sp_addrolemember N’RSExecRole’, N’WRON023\SQLSRVReporting’

GO

 

–Scripting Object and Statement Permissions

USE [ReportServer$KEYKOTempDB]

GO

Grant CONNECT ON Database::[ReportServer$KEYKOTempDB] TO [WRON023\SQLSRVReporting] 

GO

 

 

/*** WARNING: SQLpermssions is freeware – use it at your own risk. ***/

/*** There are potential security issues with copying logins and permissions ***/

/*** from one SQL Server to another. By using SQLpermissions, you acknowledge ***/

/*** and knowingly accept these risks. It is recommended that users take steps ***/

/*** to thoroughly evaluate the necessity of replicating logins and permissions ***/

/*** before using this tool. Idera shall not be liable for any damages you may ***/

/*** sustain by using this tool, whether direct, indirect, special, incidental ***/

/*** or consequential, even if it has been advised of the possibility of such damages.***/

[EN] Sunday Market–SQL Permissions by Idera (part one)

CaptureSQLPermissionThe third (and fourth) episodes in my non-frequently series: Sunday Market is about Idera too. Yes I know, not only Idera giving You free tools, I’ll try show other tools in future episodes. But this tim I’m writing about nice tool for checking permissions for our SQL Server.

If You need (like some of my client or collegues) move logins and permissions between servers, providing simple audity (like view logins and permissions) or scripting (Yes it’s for You ScriptingGuys), You can use free SQL Premission 2.0 tool. In this case I’ll show in few pictures and some functions of free Idera product SQL Permission s2.0. For free download You must:
>> go to Idera SQL Permissions 2.0 site HERE
>> fill registration data
>> checking Your mail box for download link

Very easy, isn’t? Just exactly same process like in first two episodes. When You get software, just install it:
 
PHASE ONE:
Capture_Idera_PermissionCheck_01
Pic.1 | Pre-Installation software checking for SQL Server 2005 Backward Compability Components (I’ll write shortly in next post about BCC)
Capture_Idera_PermissionCheck_02
Pic.2 | Installation failed – because is not supported on… Windows 7 SP1 x64
Capture_Idera_PermissionCheck_03
Pic.3 | We can install BCC before and re-run installator …
Capture_Idera_PermissionCheck_04
Pic.4 | …and if we not use SQL Server 2005, we can ignore tis eror.
 
PHASE TWO:
Capture_Idera_PermissionCheck_05
Pic.5 | Installation process is typical
Capture_Idera_PermissionCheck_06
Pic.6 | Welcome screen…
Capture_Idera_PermissionCheck_07
Pic.7| Licensing agreement…
Capture_Idera_PermissionCheck_08
Pic.8 | User name, organizasion and… first permission
Capture_Idera_PermissionCheck_09
Pic.9 | We can choose destination folder…
Capture_Idera_PermissionCheck_10
Pic.10 | …and run software
Capture_Idera_PermissionCheck_11
Pic.11 | At the end we can finish only, or finish and run application
 
PHASE THREE:
Capture_Idera_PermissionCheck_12
Pic.12 | First window with parameters
Capture_Idera_PermissionCheck_13
Pic.13 | When we click Select SQL Server Instances we have possibility to choose one from the list
Capture_Idera_PermissionCheck_14
Pic.14 | We must connect to the Instance…
Capture_Idera_PermissionCheck_15
Pic.15 | It’s good way to check connections
Capture_Idera_PermissionCheck_16
Pic.16 | Next we can choose which database and which user are interesting for us (all ar one from many or many from many)
Capture_Idera_PermissionCheck_17
Pic.17 | Next we should choose destination folder…
Capture_Idera_PermissionCheck_18

Pic.18 | and at the end we get some information about risk of use

It’s almost all, except… the result of our SQL Permissions check. Lets go to the second post with the code…