Bonjour,
Je veux extraire ts les login de mon server avec les privilèges/rôles associés ....?
MERCI
A+
Bonjour,
Je veux extraire ts les login de mon server avec les privilèges/rôles associés ....?
MERCI
A+
SDR.
"ceux qui vivent, ce sont ceux qui luttent."
ben là, il existe des dizaines de scripts disponibles via google...
J'ai déjà celui de Frederic
A+
SDR.
"ceux qui vivent, ce sont ceux qui luttent."
Tu as ça : https://dbatools.io/commands/#Login
J'ai ça pour les créer aussi :
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 -- Scripting Out the Logins, Server Role Assignments, and Server Permissions -- ************************************************************************************************************************ -- Copyright © 2015 by JP Chen of DatAvail Corporation -- This script is free for non-commercial purposes with no warranties. -- CRITICAL NOTE: Youll need to change your results to display more characters in the query result. -- Under Tools > Options > Query Results > SQL Server > Results to Text to increase the maximum number of characters -- returned to 8192 the maximum or to a number high enough to prevent the results being truncated. -- ************************************************************************************************************************ SET NOCOUNT ON -- Scripting Out the Logins To Be Created SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+ CASE WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = ' + CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END ELSE ' FROM WINDOWS WITH' END +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --] FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL ON SP.principal_id = SL.principal_id WHERE SP.type IN ('S','G','U') AND SP.name NOT LIKE '##%##' AND SP.name NOT LIKE 'NT AUTHORITY%' AND SP.name NOT LIKE 'NT SERVICE%' AND SP.name <> ('sa'); -- Scripting Out the Role Membership to Be Added SELECT 'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + ''' ' AS [-- Server Roles the Logins Need to be Added --] FROM master.sys.server_role_members SRM JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id WHERE SL.type IN ('S','G','U') AND SL.name NOT LIKE '##%##' AND SL.name NOT LIKE 'NT AUTHORITY%' AND SL.name NOT LIKE 'NT SERVICE%' AND SL.name <> ('sa'); -- Scripting out the Permissions to Be Granted SELECT CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' THEN SrvPerm.state_desc ELSE 'GRANT' END + ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' + CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' THEN '' ELSE ' WITH GRANT OPTION' END collate database_default AS [-- Server Level Permissions to Be Granted --] FROM sys.server_permissions AS SrvPerm JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id WHERE SP.type IN ( 'S', 'U', 'G' ) AND SP.name NOT LIKE '##%##' AND SP.name NOT LIKE 'NT AUTHORITY%' AND SP.name NOT LIKE 'NT SERVICE%' AND SP.name <> ('sa'); SET NOCOUNT OFF
Ce que nous avons fait pour nous-même meurt avec nous, ce que nous avons fait pour les autres et le monde est immortel. Albert Pike
http://www.datacrossroad.be
Vous avez un bloqueur de publicités installé.
Le Club Developpez.com n'affiche que des publicités IT, discrètes et non intrusives.
Afin que nous puissions continuer à vous fournir gratuitement du contenu de qualité, merci de nous soutenir en désactivant votre bloqueur de publicités sur Developpez.com.
Partager