SQL Server – Deny Users Permission to Create Tables in Master

master-system-databasepermissionsSecuritysql serversql-server-2012

I have noticed that I can create tables, schemas, stored procedures etc. the master database.

I don't know what is granting this permission. It's annoying, because when we open a *.sql file in SSMS, it's related by default to master. If a CREATE operation is executed without noticing that and setting the correct database context, the table is created in master.

If we didn't have the permission, we'd get an error, notice it and change the database context. With the permission, the table is created and we only see the mistake later, and need to drop it and recreate. I noticed that there are already some tables created by mistake in master.

I want to revoke that permission from every human user, but I fear breaking something. I don't know if some SQL Server routine needs permission to create there.

What's the best way to revoke create permission from master without breaking SQL Server?

Best Answer

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.

Related Question