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