Grant read access to my SQL Server database to all future SQL users [closed]

Posted on

Question :

Can I configure my SQL Server 2014 database so that any login that will be created in the future will have read access to it automatically?

Answer :

To add on my comment, you can create a trigger on a database level and add user to desired role automatically when you create a user. You can add a custom one, but for example sake i added db_datareader.

Example :

    create trigger tg_AddUserToRole on database after CREATE_USER
    as
    declare @LoginName nvarchar(30)
    set @LoginName =  (select EVENTDATA().value('(EVENT_INSTANCE/ObjectName)[1]','nvarchar(30)'))
    IF EXISTS(select 1 from sys.sysusers where name = @LoginName and  issqluser = 1)
    exec sp_addrolemember 'db_datareader', @LoginName

For new databases, add the user in the model database. This is used as the template for all new databases.

USE model
CREATE USER ... FROM LOGIN...
EXEC sp_addrolemember 'db_datareader', '...'

Copied from: https://stackoverflow.com/questions/3550497/sql-server-give-a-login-permission-for-read-access-to-all-existing-and-future

It’s not clear from your question if you want to distinguish between logins with SQL Server Authentication and Windows Authentication.

If you just want any user (every future user included) of this database to be able to read the data just
grant SELECT to public database role.

If it’s important to you that the principal is not Windows principal, in another answer with a DDL-trigger you should check if the login is authenticated by SQL Server.

Leave a Reply

Your email address will not be published.