Assign Permissions to a SQL User

Question

I have active directory on my company, I have some login users created at the level of the SQL Server (2014) that I need to propagate to all my databases on that server. I want to create a read only user and also a read write user on sql server. So I created one user USER_R and another USER_RW, that match with the domain users, on my USER_R, I want to assign db_datareader and db_denydatawriter and then select all my dbs on the User Mapping section and click OK, this will create USER_R in all my dbs.

Question 1, how to automatically manage this in case I restore a new db on that server and I want to assign the user USER_R?

Question 2, now that I have the user USER_R, a user A want to connect to management studio, he doesn't see any table or stored procedure, what's missing to let them see all objects and besides be read only?

Answer 1

Question 1, how to automatically manage this in case I restore a new db on that server and I want to assign the user USER_R?

As described here you could add the user to the model database to automatically be added to other, created databases.

However, this will not work for restored databases.

USE [model]
GO
CREATE USER [testlogin] FOR LOGIN [testlogin]
GO
USE [model]
GO
ALTER ROLE [db_datareader] ADD MEMBER [testlogin]
GO

Restored databases are hard to track, more information on why life is hard in this article by Erik Darling.

Unfortunately, triggers won't directly work on restored databases.

If you where on SQL Server 2016, you could try and cook something up with an extended event on backup_restore_progress_trace, but that does not help you here.

In this case I would just add the user creation to your restore process.

Either with dynamic SQL if you have to

DECLARE @SQL nvarchar(max)
  SET @SQL =
   '
        USE ' + QUOTENAME(@RestoredDatabaseName) + '

IF NOT EXISTS (
SELECT * FROM sys.sysusers where name = ''testlogin'')
BEGIN
CREATE USER [testlogin] FOR LOGIN [testlogin];
ALTER ROLE [db_datareader] ADD MEMBER [testlogin];
END
'

EXEC(@SQL);

(Only use dynamic sql if needed)

Or without dynamic SQL if it is not needed

USE NewlyRestoredDatabase
GO
IF NOT EXISTS (
SELECT * FROM sys.sysusers where name = 'testlogin')
BEGIN
CREATE USER [testlogin] FOR LOGIN [testlogin];
ALTER ROLE [db_datareader] ADD MEMBER [testlogin];
END

Additionally, you could run a schedule a job to check if the user exists in all databases.

While doing all this, if the user already exists in the database, you have to watch out for orphaned users, and act accordingly.

If the user already exists in the restored database

Use NewlyRestoredDatabase
ALTER USER testlogin WITH LOGIN = testlogin

Question 2, now that I have the user USER_R, a user A want to connect to management studio, he doesn't see any table or stored procedure, what's missing to let them see all objects and besides be read only?

I am guessing that this user needs to be able to see all objects, and what they are? Like the T-SQL statements inside a stored procedure?

To do this, GRANT VIEW ANY DEFINITION to the login userA

USE master 
GO 
GRANT VIEW ANY DEFINITION TO userA;

The login still needs a mapped user to the databases that it needs to access, and be added to the public role (default).