How to add read only access to all databases

  • Hi,

    I have created a new User Login in SQL Server 2000. This user should have access to every database, even databases created in the future. I could not find a good way to grant this kind of access.

    I used this script to try to grant read only access to all databases:

    exec sp_MSforeachDB 'exec ?..sp_grantdbaccess ''{login}''

    exec ?..sp_addrolemember ''db_datareader'', ''{login}'''

    But now, when I click to view properties of the Login using Enterprise Manager under Security/Logins, I recieve the following error.

    Error 21776: [SQL-DMO]The name '{login}' was not found in the Users collection. If the

    name is a qualified name, use [] to separate various parts of the name, and try again.

    {login} is a placeholder for the actual Login name.

    Is there a problem with the script I'm using? What is really causing this error? I can't even delete the Login using Enterprise Manager; I have to call sp_dropuser on each database first...

    Thanks,

    Michael

  • I'm not sure if your script has a problem. I think you need this to be two separate batches, so a GO might be needed between these commands.

    However, there's no way to grant read only access to future logins/users. That would have to be done for every new database.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply