SQL Server Security – Difference Between Membership and Securables

Since the status of the database is Standby, I'm assuming this database is a Log Shipping secondary. If that's the case, the database cannot be written to at all on the secondary (making it writable would break Log Shipping).

Also, since you said that other databases (presumably not read-only) have correct mappings, I'm assuming you're using a SQL login. If that's the case, all you should have to do is recreate the login on the secondary server using CREATE LOGIN ... WITH SID to match the SID from the primary (look in sys.server_principals on the primary server to find it).

On the other hand, if you're using Active Directory for the login, you'll have to add the user principal to the primary database so that when it comes across to the secondary in a log backup, there's no need to do any remapping (note: depending on the situation, this may be a lot easier said than done!).

By default, no direct access is permitted to either tables or stored procedures. One must explicitly grant access in order for non-privileged users to have access. The best practice to simplify security administration is to grant permissions to roles, and control access via role membership.

If you want to restrict access only via stored procedures, take a look at ownership chaining (https://technet.microsoft.com/en-us/library/ms188676). This allows you to grant execute permissions to individual roles/users without allowing direct access to underlying tables as long as the object involves have the same owner (default is dbo).

Using ownership chaining in your scenario, you could grant only SELECT permission on the tables, with updates allowed only by users with execute permission on the update procs. No direct table modifications would be allowed.

Edit:

Below is an ownership chaining example. Note that neither user can update the table directly and the write user can update the table only using the stored procedure. The implication of ownership chaining is that when you grant execute permissions on a stored procedure, users can access all of the encapsulated objects without direct object permissions as long as the objects have the same owner.

--create objects
CREATE TABLE dbo.MyTable(
      MyTableID int IDENTITY NOT NULL 
        CONSTRAINT PK_MyTable PRIMARY KEY
    , SomeData int
    );
GO

CREATE PROC dbo.UpdateMyTable
      @MyTableID int
    , @SomeData int
AS
UPDATE dbo.MyTable
SET SomeData = @SomeData
WHERE MyTableID = @MyTableID;
GO

--create roles
CREATE ROLE TableReaderRole;
CREATE ROLE TableWriterRole;
GO

--grant permissions to roles
GRANT SELECT ON dbo.MyTable TO TableReaderRole;
GRANT EXECUTE ON dbo.UpdateMyTable TO TableWriterRole;
GO

--create user with only read permissions
CREATE USER SomeReadOnlyUser FOR LOGIN SomeReadOnlyUser;
ALTER ROLE TableReaderRole
    ADD MEMBER SomeReadOnlyUser;
GO

--create user with both read and writer permissions
CREATE USER SomeAllowedWriteUser FOR LOGIN SomeAllowedWriteUser;
ALTER ROLE TableReaderRole
    ADD MEMBER SomeAllowedWriteUser;
ALTER ROLE TableWriterRole
    ADD MEMBER SomeAllowedWriteUser;
GO

Be aware that it is the owner rather than the schema name that is relevant for ownership chaining. If the table and proc were in different schemas, the ownership chain would still be unbroken as long as the schemas had the same owner (AUTHORIZATION). The object owner is typically inherited from the schema owner.