Re: SQL Local Account Permission - Read Only on System Tables and

I'd like to create a new role (database-level, I guess, as I don't think you
can create your own server-level roles) and grant it VIEW SERVER STATE
permission. Then, I can assign all of our developers to that role so they
can do things like use sp_who2 (and see all current processes).

But, do you know if this is possible? That is, can I grant this particular
permission to a role? I'd rather do it this way than grant the permission to
each individual developer's login.

Thank you!
Susan Jones

"Russell Fields" wrote:

ASP_Paul,

SQL Server 2005 tends to lock down information that SQL Server 2000 gave
away freely. So, most users can only see things that directly connect to
their rights. Other rights can be granted, if a logon needs more
information than the default. Look at some of the new types of GRANT
permissions.

Server Level:
VIEW SERVER STATE
VIEW ANY DEFINITION
VIEW ANY DATABASE

Database Level:
VIEW DATABASE STATE
VIEW DEFINITION

Only use these as appropriate to your security standards, of course.

RLF


"ASP_Paul" <ASP_Paul@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CEB3585E-89C7-48E8-BEE7-1165A0DACC9B@xxxxxxxxxxxxxxxx
I have been given the task of creating a local SQL account (Application
account) on our 2005 SQL server for the use of a custom application that
will
look at the status of jobs/packages/etc... The application user id has
been
granted 'select access' to a list of tables provided by our development
staff. However I am running into problems with 2 system views. I granted
select on each view and have the following problem:

SQL:
select * from master..syslogins
Results:
Only 2 records, and both records are the 2 SQL accounts of SA and the
Application ID I created.

SQL
select * from dbo.sysschedules_localserver_view
Results:
No rows. I know there are 40 records when selected as an Admin account.

I was able to work around the problem by created a stored proc as such:

CREATE PROCEDURE dbo.aspSyslogins
WITH EXECUTE AS OWNER
AS
select * from master..syslogins
GO

GRANT EXECUTE ON dbo.aspSyslogins TO public
GO

However, I would rather figure out why I can not select directly against
the
view as this was this was the request given to me by our Dev staff.




.

Relevant Pages

  • Re: Model Driven Architecture, bussiness rules in DB?
    ... For instance when you grant access to a table you should be ... data management, integrity management, data manipulation, security, session ... *declared* exclusively in the database. ... would have a dedicated connection to an anonymous DBMS server, ...
    (comp.databases.ingres)
  • Re: shared access folder problem
    ... > have access to server shares. ... You would have to grant annoymous access ... >> database in the virtual directory....now thats its about done, ... >> "Serverx is a file server, ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Remote Access to Server
    ... grant the user allow permission to the share folder, ... >Thread-Topic: Remote Access to Server ... I will give the VPN option ... Grant permissions to the user or group ...
    (microsoft.public.windows.server.sbs)
  • Re: MSDE 2000 with Access 2003: The First steps
    ... the benefits of using Integrated Security for your SQL Server logins. ... built-in server roles, like sysadmin grant general permissions server-wide, ... database, that user is the database owner for that database, so that ...
    (microsoft.public.sqlserver.msde)
  • Re: can I use GPO for remote folder management?
    ... > that group to have the permissions you want him able ... > to grant to others. ... Folder and subfolders. ... >> we have one stand alone 2003 server. ...
    (microsoft.public.win2000.group_policy)
Loading