{koprowskit.eu} My MVP blog

The 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.***/