15

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.)

  • I'm puzzled; is not the default on creating a new database is database-server users with the default permission set can't write to it? – Joshua Jul 7 at 22:33
  • Yes, but that doesn't mean someone can't be part of a group that's given access. Denies take precedence over grants, thus the question. If you don't understand it, no worries, you can carry on - explaining security is a little beyond what I can do in a comment here. – Brent Ozar Jul 8 at 2:32
  • I know what denies are. I'm just not accustomed to seeing them used unless they're literally the only tool for the job. – Joshua Jul 8 at 3:27
15

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 ;
| improve this answer | |
  • 1
    That's a good idea! I'm fine with the execution time. I might get a little fancier to make it dynamically driven for the login names, but that'll do. Thanks! – Brent Ozar Jul 6 at 22:35
0

can't you use a login trigger and a deny on IUD ? Everytime the user logs in I guess it would need to iterate over the databases and add the DENYs any newly added database.

| improve this answer | |
  • 1
    That would be pretty ugly in performance terms - some servers have hundreds or thousands of logins per second, and iterating over databases & security objects could easily present concurrency problems. Neat idea though. – Brent Ozar Jul 6 at 14:53
  • Sounds like what you really want is a new sort of endpoint that only allows READ-ONLY access - something for the feedback site ? – Mo64 Jul 6 at 15:54
  • No, a new endpoint would be a giant pain in the rear for ports, firewalls, etc. This is just a permissions question. Thanks though! – Brent Ozar Jul 6 at 15:56
0

Someone may be able to make this simpler but here is my approach:

  1. Create Server Role for "Read-Only Admins" and assign the following permissions to allow for server-wide permissions of specific features such as the ability to see the definition of all objects and view the server state. This also creates a "bucket" for what users you want to assign to the individual database roles:

    USE [master]
    GO
    CREATE SERVER ROLE [ReadOnlyAdmins]
    GO
    GRANT CONNECT SQL TO [ReadOnlyAdmins]
    GO
    GRANT VIEW ANY DATABASE TO [ReadOnlyAdmins]
    GO
    GRANT VIEW ANY DEFINITION TO [ReadOnlyAdmins]
    GO
    GRANT VIEW SERVER STATE TO [ReadOnlyAdmins]
    GO
    
  2. Create a stored procedure on [master] to create/update a database-specific user group which grants reads but denies writes on the specified database. I am using a stored proc since you will most likely need to run within the context of the specified database so dynamic sql is the first solution that comes to mind. Note this solution forces you to use a specific group name, you can change it to whatever you like.

EDIT: As noted in another solution, the stored procedure needs to check if the database being added/updated is in an ONLINE mode. If not should it just loop and check every 5 seconds or so?

    CREATE PROCEDURE dbo.CreateDBReadOnlyAdmins
    (
         @dbName NVARCHAR(128)
    )
    AS
    BEGIN
    SET NOCOUNT ON;
    DECLARE @dbState NVARCHAR(500)
    select @dbState = state_desc from sys.databases where name = @dbName
    WHILE(@dbState <> 'ONLINE')
       BEGIN
          WAITFOR DELAY '00:00:05'
          select @dbState = state_desc from sys.databases where name = @dbName 
       END 
    DECLARE @sql NVARCHAR(MAX), @max_id int, @id int, @retval int, @ParmDefinition                 nvarchar(500);
    --Check to see if the Database Role has been created or not.  Add it if not.
    SET @sql = 'SET NOCOUNT ON;SELECT 1 FROM ' + @dbName + '.sys.database_principals                 WHERE type_desc = ''DATABASE_ROLE'' AND name = ''ReadOnlyAdmins'';'
    SET @ParmDefinition = N'@retvalOUT int OUTPUT';
    EXEC sp_executesql @SQL, @ParmDefinition, @retvalOUT=@retval OUTPUT;
    SELECT @id = ISNULL(@retval,0);
    IF (@id = 0)
        BEGIN 
            SET @sql = 'USE [' + @dbName + '];' +
            'CREATE ROLE [ReadOnlyAdmins];' +
            'GRANT SELECT ON ALL TO [ReadOnlyAdmins];' +
            'DENY INSERT, UPDATE, DELETE ON ALL TO [ReadOnlyAdmins];';
            EXEC @sql
            --SELECT (@sql)
        END
    
    
    IF(OBJECT_ID('tempdb..#role_members') IS NOT NULL) BEGIN DROP TABLE #role_members END;
    --Add all members of the ReadOnlyAdmins Server Role to the db-specific role.
    SELECT ROW_NUMBER() OVER (ORDER BY member.name) as rowid
        ,'ALTER ROLE [ReadOnlyAdmins] ADD MEMBER [' + member.name + '];' AS MemberName
    into #role_members
    FROM sys.server_role_members  
    JOIN sys.server_principals AS role  
        ON sys.server_role_members.role_principal_id = role.principal_id  
    JOIN sys.server_principals AS member  
        ON sys.server_role_members.member_principal_id = member.principal_id
    WHERE role.name = 'ReadOnlyAdmins';
    
    SET @sql = 'USE [' + @dbName + '];'
    
    select @max_id = MAX(rowid) from #role_members
    SET @id = 1
    
    WHILE @id <= @max_id
        BEGIN
            SELECT @sql = @sql + MemberName from #role_members where rowid = @id
            EXEC @sql
            --SELECT @sql
            SET @id = @id + 1
        END
    END
    GO
  1. Next is to add a ddl trigger to cover all CREATE_DATABASE and ALTER_DATABASE events. This trigger needs to be finely tuned to handle high availability scenarios as well as random changes to the compatibility, recovery model, etc. Also the trigger is going to parse out the database name. I am stealing from [Bob Pusateri]https://www.bobpusateri.com/archive/2018/09/a-tale-of-a-trigger/ for the HA handling. Also I am unsure how to prevent the trigger from firing on non-invasive updates (such as compatibility, recovery model, user mode, etc). I guess a smarter person will need to handle that. :)

    CREATE TRIGGER AddReadOnlyAdminsToDBs
    ON ALL SERVER
    FOR CREATE_DATABASE, ALTER_DATABASE
    AS
    BEGIN
        DECLARE @xml XML = EVENTDATA();
        DECLARE @var_xml NVARCHAR(128);
        DECLARE @error_msg VARCHAR(1024);
    
        SET @var_xml.value('(EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(128)') 
    
        --uncomment the below line if you are running HADR
        --If sys.fn_hadr_is_primary_replica(@var_xml) = 1   
        BEGIN  
            EXEC master.dbo.CreateDBReadOnlyAdmins @var_xml 
        END  
    END
    GO
    
  2. Now the above covers whenever you add a new or update a database on the instance. What do you do when you are merely adding a new login to the SERVER ROLE in the above? Off the top of my head is creating another stored proc (or merely altering the above stored proc to handle ALL local databases and then adding all users in the SERVER ROLE to all non-system databases). Then couple that with another DDL trigger that only looks at the ADD_SERVER_ROLE_MEMBER event and then only at the specific SERVER ROLE "ReadOnlyAdmins".

Other holes I have identified:

  • How to handle SQL Agent. I mean you could custom create a db-specific role for MSDB to cover things like checking job status, seeing the job log, etc.
  • How to handle Replication. I am unsure on how to handle this specific situation off the top of my head. I would need to research it more.
  • Could this be better served by some policy based management? Once again, I would need to do more research.

Thanks.

| improve this answer | |
  • Thanks for the in-depth answer, but these don't really work. For example, when you restore a database, it's created, but it's not online yet, so you can't do the deny. Rob D's answer is a better fit. Thanks though! – Brent Ozar Jul 6 at 22:36
0

I found this maybe this one can help with the restore part.

Automatically Execute Stored Procedure After Any RESTORE DATABASE Event

DECLARE @fn VARCHAR(MAX); 
SELECT @fn = SUBSTRING([path], 0, LEN([path])-CHARINDEX(CHAR(92), REVERSE([path]))+1) + CHAR(92) + 'Log.trc' 
FROM sys.traces WHERE is_default = 1; 

SELECT DatabaseName, StartTime, TextData 
FROM sys.fn_trace_gettable(@fn, DEFAULT) 
WHERE EventClass = 115 
 AND TextData LIKE '%RESTORE%'; -- since can't differentiate between backup/restore
| improve this answer | |
-1

I think would use a server scoped trigger, and any Create Database or Restore database commands would immediately follow a create user and deny IUD on the newly created or restored database:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver15

CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS

GO

| improve this answer | |
  • This is the most solid starting point I've heard so far, except I think it's going to fail when databases are created during the restore process, seeding new AG replicas, etc. – Brent Ozar Jul 6 at 15:11
  • Yeah, just did a quick test: in order to deny writes, you have to USE the database in order to run an ALTER ROLE [db_denydatawriter] command in it, and that doesn't work when the database is restoring. Dang! I had high hopes for this method. – Brent Ozar Jul 6 at 15:57
  • Another option could be to use a combination of extended events and message broker to capture the new or restored database, forward the event to a queue which in turn fires off the DCL. I would hate to go down this road, but I think this leads to a solution. – Rusty Jul 6 at 16:09
  • Yeah, I'm with you: I think that'd be a valid solution, and I'd hate to be the guy who implements it, but...it's valid, heh. – Brent Ozar Jul 6 at 16:18
-1

I had a use case come up within work that, seems similar in scope, in which I was told I had to allow a certain account read access on new and existing databases. I did not want to allow the user the ability to make changes at the database and server levels.

After researching how to go about this, I found that starting in SQL server 2016 you can create a Custom Server role that you can then apply two grants to.

CONNECT ANY DATABASE and SELECT ALL USER SECURABLES

Between these two grants you are granting read only access to all databases that currently exist, newly created, or have been restored from backup on a given instance. Deny writes is implied because you have to grant the server role the permissions to allow writes.

| improve this answer | |
  • Unfortunately no, that only gives them read access. It doesn't stop them from writing, like if a newly restored database has granted write permissions to the public role. Thanks though. – Brent Ozar Jul 6 at 15:12
  • I would like to refine my approach then, and I look forward to seeing the correct answer. – Conamin Jul 6 at 15:17
  • Me too! Isn't it tricky? I'm amazed at how hard this problem turns out to be. The hard things are easy, and the seemingly-easy things are hard. Thanks anyway though! – Brent Ozar Jul 6 at 15:20
-1

From the top of my head, maybe create a login and deny everything to it and only allow select/insert/updates/deletes via custom stored procs?

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.