Discussion :

[Lolotte95]

Bonjour,
Après restaure de base SQL SERVER en DEV à partir de la PROD, je me retrouve avec les droits définis en PROD.
Aussi j'aimerais savoir s'il n'existerait pas une instruction/procédure stokée permettant de restaurer les droits après restaure ou bien d'effectuer un restaure sans toucher aux droits ?
A l'heure actuelle je prend photo avant restaure et remets "à la main" les autorisation...un peu fastidieux quand même et peut-être même dangereux si je dois effectuer un restaure en prod un jour...
Merci de votre aide.

[mikedavem]

Si vous parlez des droits propres à la base de données (utilisateurs etc...) non puisque une restauration viendra écraser le paramétrage existant. Il faut donc veiller à bien sauvegarder votre configuration avant restauration.

++

[Ptit_Dje]

Si voulez bien etre une testeuse de mon script, vous pouvez essayer ceci (copier le script dans un fichier Permissions.ps1 et executer le script depuis une machine ayant powershell 2.0 et sqlps installe) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
 
#
# Loads the SQL Server Management Objects (SMO)
#
$ErrorActionPreference = "Stop"
$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"
if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
    throw "SQL Server Provider for Windows PowerShell is not installed."
}
else
{
    $item = Get-ItemProperty $sqlpsreg
    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}
 
$assemblylist = 
"Microsoft.SqlServer.Management.Common",
"Microsoft.SqlServer.Smo",
"Microsoft.SqlServer.Dmf ",
"Microsoft.SqlServer.Instapi ",
"Microsoft.SqlServer.SqlWmiManagement ",
"Microsoft.SqlServer.ConnectionInfo ",
"Microsoft.SqlServer.SmoExtended ",
"Microsoft.SqlServer.SqlTDiagM ",
"Microsoft.SqlServer.SString ",
"Microsoft.SqlServer.Management.RegisteredServers ",
"Microsoft.SqlServer.Management.Sdk.Sfc ",
"Microsoft.SqlServer.SqlEnum ",
"Microsoft.SqlServer.RegSvrEnum ",
"Microsoft.SqlServer.WmiEnum ",
"Microsoft.SqlServer.ServiceBrokerEnum ",
"Microsoft.SqlServer.ConnectionInfoExtended ",
"Microsoft.SqlServer.Management.Collector ",
"Microsoft.SqlServer.Management.CollectorEnum",
"Microsoft.SqlServer.Management.Dac",
"Microsoft.SqlServer.Management.DacEnum",
"Microsoft.SqlServer.Management.Utility"
 
foreach ($asm in $assemblylist)
{
    $asm = [Reflection.Assembly]::LoadWithPartialName($asm)
}
 
$serverName= read-host "Please enter the server name where you is located the database (i.e.:server1\instance2)"
$databaseName= read-host "Please enter the database name you want to list the permissions (i.e.:DatabaseA)"
$serverConnection = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$serverConnection.ServerInstance=$serverName
$server = new-object Microsoft.SqlServer.Management.SMO.Server($serverConnection)
$database = $server.Databases[$databaseName]
#Database user database permissions/object permissions
foreach($user in $database.Users)
{
 foreach($databasePermission in $database.EnumDatabasePermissions($user.Name))
 {
  $Grantee = $databasePermission.Grantee
  Write-Host $databasePermission.PermissionState $databasePermission.PermissionType "TO [$Grantee]" 
 }
 foreach($objectPermission in $database.EnumObjectPermissions($user.Name))
 {
  #Write-Host $objectPermission.PermissionState $objectPermission.PermissionType "ON" $objectPermission.ObjectName "TO" $objectPermission.Grantee
  $permissionState = $objectPermission.PermissionState
  $PermissionType = $objectPermission.PermissionType
  $ObjectSchema = $objectPermission.ObjectSchema
  $ObjectName = $objectPermission.ObjectName
  $Grantee = $objectPermission.Grantee
 
  Write-Host "$PermissionState $PermissionType ON [$ObjectSchema].[$ObjectName] TO [$Grantee]"
 }
}
#Create user defined roles
foreach($Role in $database.Roles)
{
 if($Role.IsFixedRole  -ne 1 -and $Role.name -ne "Public")
 {
  write-host "create role $Role"
 
   foreach($objectPermission in $database.EnumObjectPermissions($Role.name))
   {
    $permissionState = $objectPermission.PermissionState
    $PermissionType = $objectPermission.PermissionType
    $ObjectSchema = $objectPermission.ObjectSchema
    $ObjectName = $objectPermission.ObjectName
    $Grantee = $objectPermission.Grantee
 
    Write-Host "$PermissionState $PermissionType ON [$ObjectSchema].[$ObjectName] TO [$Grantee]"
    #Write-Host $objectPermission.PermissionState $objectPermission.PermissionType "ON " $objectPermission.ObjectSchema.$objectPermission.ObjectName "TO" $objectPermission.Grantee
   }
 }
}
#add role members
foreach($Role in $database.Roles)
{
 $roleName = $Role.name
 foreach($roleMember in $Role.EnumMembers())
 {
  Write-Host "sp_addrolemember '$roleName','$roleMember'"
 }
}
$server.ConnectionContext.Disconnect()

Le script vous demandera le servername\instanceName et le nom de la base de donnees de laquelle vous souhaitez scripter les permissions.
Il generera en output (dans la fenetre powershell) la liste des permissions actuellement definies sur la base.
Bien qu'il puisse repondre a pas mal de besoins, il n'est pas garanti 100%. (Je dois encore trouver le temps de fignoler/optimiser et tester completement)

Tout feedback est le bienvenu, ce qui m'aidera aussi a avancer la dessus (Y compris si d'autres testeurs sont volontaires )