Re: SQL Local Account Permission - Read Only on System Tables and
- From: "Russell Fields" <russellfields@xxxxxxxxxx>
- Date: Wed, 9 Jan 2008 13:06:11 -0500
Susan,
You cannot create a system role, but there is a workaround.
Create a Windows group on the domain and have your developer logins added to
that group. Then give the Window group login rights to the server and grant
VIEW SERVER STATE to that one login. No other rights need to be granted to
the group. I usually call my Windows groups something like:
MyDomain\SQLServerNameFixedServerRoleViewServerState
All members of the Windows group will then inherit the rights when they
login, even if they have also been granted direct login rights to the
server.
Russell Fields
"sjones" <sjones@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:92260ED4-1FFE-4659-B0CC-1A90E56F1EEA@xxxxxxxxxxxxxxxx
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.
.
- References:
- Prev by Date: Re: SQL Local Account Permission - Read Only on System Tables and
- Next by Date: Re: General permission question
- Previous by thread: Re: SQL Local Account Permission - Read Only on System Tables and
- Next by thread: Re: General permission question
- Index(es):
Relevant Pages
|
