May 15, 2002 at 2:19 pm
I have an instance of SQL2000 running on a server with 15 user DB's. I have a DB called DBA with proc/func ect.
I want to be able to execute "grant select on gmi..deals to developers" from the DBA database running a procedure. I keep getting "You can only grant on the current database".
Can this be done?
Thanks,
Joe
May 15, 2002 at 2:50 pm
You'll have to loop through the dbs and execute once per db. Alternative would be to add the user to a server role.
Andy
May 15, 2002 at 3:28 pm
You could also create a script that checks for the existance of the table and does this then use sp_MSForEachDB to speed you along.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 15, 2002 at 3:55 pm
Just a thought. If you are doing this anyway, I'd create a view in each DB that points to the other db. Easier than rewriting lots of queries of the db moves or you need another instance. Then use a role to grant permissions.
Steve Jones
May 16, 2002 at 6:38 am
Thank you for the help.
I found the sp_msforeachdb in the object browser, but there is not any information from the "Help: online books".
Can you recommend a good reference book? I am not looking for "How to", but something I can look up "sp_MSForEachDB" and get the information on it.
Thanks again,
Joe
May 16, 2002 at 7:17 am
Its undocumented, but this article by Brian should get you going:
http://www.sqlservercentral.com/columnists/bknight/sp_msforeachdb.asp
It will work, but I like Steve's suggestion of the view better.
Andy
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply