You'll need to determine how the logins have access to run DDL in master. The following query will return a list of logins with server-level role membership:
SELECT ServerName = @@SERVERNAME
, RoleName = roles.name
, MemberName = members.name
, IsEnabled = CASE WHEN members.is_disabled = 1 THEN 0 ELSE 1 END
FROM sys.server_role_members srm
INNER JOIN sys.server_principals roles ON srm.role_principal_id = roles.principal_id
INNER JOIN sys.server_principals members on srm.member_principal_id = members.principal_id
WHERE members.name NOT LIKE 'NT %' --exclude builtin principals
ORDER BY roles.name
, members.name;
You can use xp_logininfo
to understand how a particular Windows account has access to SQL Server:
EXEC xp_logininfo @acctname = 'DOMAIN\Username', @option = 'all';
If the people in question have membership in the sysadmin
role, you won't be able to prevent them from running DDL statements in master
(or anywhere else for that matter).
Presuming the people are not members of the sysadmin
server-role, then DENY
permissions take precedence over permissions that have been GRANTed
.
You could create a Windows security group, named "NoMasterDDL" and add your developers to that group. Create a login for the group, then DENY
permissions on the DDL statements you don't want people to perform in master.
USE master;
CREATE LOGIN [DOMAIN\NoMasterDDL] FROM WINDOWS;
CREATE USER [DOMAIN\NoMasterDDL] FOR LOGIN [DOMAIN\NoMasterDDL];
DENY ALTER ON DATABASE::master TO [DOMAIN\NoMasterDDL];
The above will prevent members of the [DOMAIN\NoMasterDDL]
group from performing any DDL type statements, such as CREATE TABLE
.
As I said in answer to your previous question, and AMtwo mentioned in a comment:
If a user is currently a member of sysadmin, any DENY
permissions will have no effect. Particularly in a development environment, developers might have such access. If so, the CONTROL SERVER
permission can be used instead; it is the same as sysadmin, but obeys DENY
permissions.
So, you can grant CONTROL SERVER
instead of making them members of the sysadmin server role, then use DENY ALTER
as shown above to prevent DDL in the master database.