SQL Server Security – How to Deny Writes in All Databases

Securitysql server

I want to create a login that is denied the ability to write to any databases – not just databases that exist today, but any newly created databases or databases that are restored from other servers.

I can't use database-only roles (not even in model), because those won't take effect on newly restored databases.

(Business purpose: I'm writing a blog post on how folks can set up a new login for themselves that has low privilege, and they can make sure that they don't accidentally have an "oops" moment when they copy/paste code or run it without a where clause.)

Best Answer

If you could live with a finite time (minimum 10-seconds) between executions to catch new/restored databases, you could create a scheduled SQL Server Agent Job and put something like this in a T-SQL Job Step:-

SET NOCOUNT ON

DECLARE @command nvarchar(max) ;

SET @command = 
N'SET NOCOUNT ON

DECLARE @UserName sysname ;

SELECT @UserName = USRS.[name] 
FROM sys.database_principals AS USRS
INNER JOIN sys.server_principals AS LGNS ON LGNS.[sid] = USRS.[sid]
WHERE LGNS.[name] = ''LoginName'' ;

IF IS_ROLEMEMBER(''db_denydatawriter'', @UserName) = 0
   BEGIN
      EXEC sp_addrolemember ''db_denydatawriter'', @UserName ;
   END ;' ;

EXEC sp_ineachdb 
   @command      = @command, 
   @state_desc   = N'ONLINE', 
   @is_read_only = 0 ;
Related Question