November 30, 2007 at 2:56 pm
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
December 2, 2007 at 1:26 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy