Prevent-Data-Exfiltration-in-Azure-SQL-Managed-Instance/Restricted Admin.sql at master · raghavender7/Prevent-Data-Exfiltration-in-Azure-SQL-Managed-Instance · GitHub

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
USE [master]
GO


-- Create a server role for Restricted Admin
CREATE SERVER ROLE [RestrictedAdmin]
GO

-- Grant the following least privilege permissions to the server role

GRANT CONNECT ANY DATABASE TO [RestrictedAdmin]
GRANT SELECT ALL USER SECURABLES TO [RestrictedAdmin]
GRANT VIEW SERVER STATE TO [RestrictedAdmin]
GRANT ALTER ANY CONNECTION TO [RestrictedAdmin]
GRANT VIEW ANY DEFINITION TO [RestrictedAdmin]
GRANT VIEW ANY DATABASE TO [RestrictedAdmin]
GRANT ALTER ANY CONNECTION TO [RestrictedAdmin]
GRANT ALTER SERVER STATE TO [RestrictedAdmin]
GRANT ALTER ANY EVENT SESSION TO [RestrictedAdmin]
GRANT ALTER TRACE TO [RestrictedAdmin]
DENY ALTER ANY DATABASE TO [RestrictedAdmin]
DENY ALTER ANY LINKED SERVER TO [RestrictedAdmin]
DENY ALTER ANY LOGIN TO [RestrictedAdmin]
DENY ALTER ANY ENDPOINT TO [RestrictedAdmin]
DENY ALTER ANY DATABASE TO [RestrictedAdmin]
DENY ALTER ANY CREDENTIAL TO [RestrictedAdmin]
DENY ALTER ANY SERVER AUDIT TO [RestrictedAdmin]
DENY ALTER ANY SERVER ROLE TO [RestrictedAdmin]
DENY ALTER SETTINGS TO [RestrictedAdmin]
DENY IMPERSONATE ANY LOGIN TO [RestrictedAdmin]
DENY ADMINISTER BULK OPERATIONS TO [RestrictedAdmin]
--GRANT VIEW DATABASE STATE TO [RestrictedAdmin]

-- Finally, Add your DBAs and other users(preferably AD groups) to this server role
ALTER SERVER ROLE [RestrictedAdmin] ADD MEMBER [service.sql]
GO