2005 sysaltfiles question

Last Post 25 Oct 2009 09:56 PM by yupsay. 7 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
mddba
New Member
New Member

--
09 Aug 2006 06: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 01:57 PM
hi mddba

did you get this sorted?
jagsandhu
New Member
New Member

--
21 Feb 2007 06: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 08: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

--
22 Feb 2007 12:41 AM
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

--
22 Feb 2007 12:44 AM
Also, mbdba has already added the user to the db_datareader role anyway
jagsandhu
New Member
New Member

--
22 Feb 2007 08: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 09: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.
You are not authorized to post a reply.

Acceptable Use Policy