SQL Server Performance Forum – Threads Archive
Access right to "all" databases
Environment: SQL Server 2005 on Windows Server 2003 I want to have a user with read only access to all databases even those that will be created or attached in the future. Is this possible? If yes, how? CanadaDBACreate a login and grant DB_DATBAREADER access to all existing databases.
For future dbs, create the user in model database…I don’t think this will work for attached dbs BUT you can test it out…
MohammedU.
Moderator
SQL-Server-Performance.com
You are right. It worked for new databases but didn’t work for attached databases. Thanks Mohammed!
quote:Originally posted by MohammedU
Create a login and grant DB_DATBAREADER access to all existing databases.
For future dbs, create the user in model database…I don’t think this will work for attached dbs BUT you can test it out…
MohammedU.
Moderator
SQL-Server-Performance.com
For future dbs, create the user in model database…I don’t think this will work for attached dbs BUT you can test it out…
MohammedU.
Moderator
SQL-Server-Performance.com
CanadaDBA
You can create a job and schedule to run every few minutes and see if any new database created and read only user does not exists to create it….
MohammedU.
Moderator
SQL-Server-Performance.com
Isn’t it possible to have a job that would be triggered if a database is attached? CanadaDBA
I think it can be done with SQL Alerts…
Fire the alerts when the db is created to execute the job… Check SQL alerts…
MohammedU.
Moderator
SQL-Server-Performance.com
Created or attached? Remember my problem is when the db is attached…
quote:Originally posted by MohammedU
I think it can be done with SQL Alerts…
Fire the alerts when the db is created to execute the job… Check SQL alerts…
MohammedU.
Moderator
SQL-Server-Performance.com
Fire the alerts when the db is created to execute the job… Check SQL alerts…
MohammedU.
Moderator
SQL-Server-Performance.com
CanadaDBA
]]>