2005 sysaltfiles question

Last Post 25 Oct 2009 08:56 PM by yupsay. 7 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
mddba
New Member
New Member

--
09 Aug 2006 05:37 AM
On SQL 2005, I have a user in the db_datareader role in the master database. I updated the user's default profile to be sys but I still can't select * from sysaltfiles. If I add the user to the server sysadmin role, I can query this table (view really). I don't want to do that though. What step am I missing here?

Thanks.
bobotahy
New Member
New Member

--
20 Feb 2007 12:57 PM
hi mddba

did you get this sorted?
jagsandhu
New Member
New Member

--
21 Feb 2007 05:16 AM
or You can grant the following:

grant view any definition to loginname

Please note that this will allow user to see all definitions.
jagsandhu
New Member
New Member

--
21 Feb 2007 07:11 AM
hi rm,


how do you mean?

not trying to be contentious here. just trying to get to the bottom of it.


if the the view any definition permission is granted a user should be able to get data from select * from sysaltfiles.
jagsandhu
New Member
New Member

--
21 Feb 2007 11:41 PM
Cheers for that rm.

The permission I put on the post was "VIEW ANY DEFINITION" and not "VIEW DEFINITION".

You might want to check it out.
jagsandhu
New Member
New Member

--
21 Feb 2007 11:44 PM
Also, mbdba has already added the user to the db_datareader role anyway
jagsandhu
New Member
New Member

--
22 Feb 2007 07:26 AM
Hi RM,

indeed it does.

"GRANT VIEW ANY DEFINITION TO LOGINNAME"

Original issue:

"On SQL 2005, I have a user in the db_datareader role in the master database. I updated the user's default profile to be sys but I still can't select * from sysaltfiles. If I add the user to the server sysadmin role, I can query this table (view really). I don't want to do that though. What step am I missing here? "


mddba has got the user in the db_datareader but he is not able to get data from sysaltfiles and he doesn't want the user to have sysadmin.

So what he needs is the following:

db_datareader in master (which he already has) OR SELECT permission on syslatfiles

+

VIEW ANY DEFINITION OR ALTER ANY DATABASE


regards

Jag
yupsay
New Member
New Member

--
25 Oct 2009 08:56 PM
30 months later this solved my prob.
For a user with public role I was able to get most of the system objects under MASTER but not [sysaltfiles] all I was getting were column headlines. After doing this - GRANT VIEW ANY DEFINITION TO dba_mon_all Its solved.


Acceptable Use Policy
---