Permissions on System Views

[fuzzyocelot]

I can’t seem to figure this out so hopefully someone has some good advice.

I’m trying to create administrative reports for DBAs to simplify our daily check of the database servers (SQL 2000 and 2005). Since we have many servers (15+), I was hoping to create a generic login across the servers that would be used for the data source connections in these reports. This way we wouldn’t have to modify lots of report data sources every time we change our dba passwords (which occurs on a regular basis). However, this whole thing is proving to be difficult.

As a starting point, I created a view in our utilities database on a development server that displays a list of all SQL logins that were recently created or updated along with some other information. The system views it uses are sys.sysdatabases and sys.syslogins. Since I have sysadmin permissions, it runs fine when I run it. However, it returns no data when I use the generic login I created. I created a user account for the generic login in the master database and utilities. I tried to give the generic login read permissions to the view I created and to the master database via db_datareader. It’s not working, though. I still don’t get any data returned when I use the generic login to select on the view. I really don’t want to grant select on the individual system views if I don’t have to.

I’m sure the system views were designed this way on purpose. How the heck do I report on system views or tables without assigning sysadmin permissions to this user? I’m stumped! Help please! Am I going about this the wrong way?

Thanks!

 

[fuzzyocelot]

I just realized why db_datareader won't work on system views/tables. It's meant for user tables only. Duh! Anyway, how else can I go about doing my reporting on system information? I'll keep Google'ing it to see what I can find, but I'd still appreciate any help!

Thanks!

 

[ptheriault]

I was hoping to create a generic login across the servers that would be used for the data source connections in these reports.

Why not create a Domain account? You could add it to each server with sysadmin rights and control the member very easily.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[fuzzyocelot]

Good idea, but I don't have the ability to create or maintain a domain account. I'd have to ask one of our network people to do it. We're in the process of going to Active Directory in the next few months anyway. So I may be able to get them to create one for general reporting purposes.

I was hoping I wouldn't have to make this account a sysadmin, though.

I sort of figured out my problem. I was trying to do this in SQL 2005. Apparently it works fine in 2000. It would be nice if I could do this sort of thing in both 2000 and 2005 without having to run different queries or to create accounts with different permission levels.

 

[mrdenny]

Grant the login the view server state permission. This will allow it to see the system views.


Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ptheriault]

Good idea, but I don't have the ability to create or maintain a domain account.

most DBA's don't have that ability. But once you've upgraded you should add the group. You'll find it much easier to manage one group instead of many Logins.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[fuzzyocelot]

Denny, the view server state permissions worked great! Thanks!!

Paul, that is a good idea. Once everything's migrated to AD, I'll work on getting a user for that group or at least create a group with a certain set of permissions. It's something to look into. Thanks!!

 

[fuzzyocelot]

Well, I knew it was too good to last. In SQL 2000, I am unable to do a select on sysaltfiles with this non-sysadmin login. I can select on sysdatabases and syslogins, though. The permissions are the same from what I can see. Is there some underlying security that's preventing this user from selecting on the sysaltfiles table?

Thanks!

 

[mrdenny]

What error are you getting when you try to select from sysaltfiles?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[fuzzyocelot]

I get this error...

Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'sysaltfiles', database 'master', owner 'dbo'.

 

[mrdenny]

You need to grant the user rights to select from the table.

grant select on sysaltfiles to [i]username[/i]

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[fuzzyocelot]

I figured as much. Since I'm not too hip on having to grant permissions to system tables, I just created a stored procedure to write the data I need to a table in a generic database. I then granted permission for the user to read that table. Thanks, though!