Permissions in SQL Server 2005 to allow users to view the Management Activity monitor?

For SQL Server 2000 we have a user login mapped to msdb with database role membership of db_datareader and public checked. This seems to allow the developers to view the Management Activity monitor. For SQL Server 2005 the same mapping is in place but the developers cannot view the Management Activity monitor. Developers are NOT granted the sysadmin role, and should not have that role.

What permissions need to be set for SQL Server 2005 to allow users to view the Management Activity monitor? They should not be allowed to take actions on the activities.

The Active Monitor tool requires VIEW SERVER STATE permission; this permission is required to select from DMVs such as sys.sysprocesses.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thank you so much! This did the trick.

Barb

|||

Does Management Studio Express support the Activity Monitor?

I've enabled VIEW SERVER STATE for admin and still can't find the Activity Monitor in the GUI.

Thanks

|||

Unfortunately I am not familiar with the different GUI tools available for SQL Express. I would recommend asking this question on the SQL Express forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=385&SiteID=1) instead.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Where do you set this: To view the Activity Monitor the user needs VIEW SERVER STATE permission on a SQL Server 2005 server.

|||

The easiest way is to just open a connection (i.e. using sqlcmd or Management Studio) using your sysadmin credentials and issue the following statement:

GRANTVIEWSERVER STATETO <<login name>>

This will grant <<login_name>> VIEW ANY STATE permission.

-Raul Garcia

SDE/T

SQL Server Engine

Permissions in SQL Server 2005 to allow users to view the Management Activity monitor?

For SQL Server 2000 we have a user login mapped to msdb with database role membership of db_datareader and public checked. This seems to allow the developers to view the Management Activity monitor. For SQL Server 2005 the same mapping is in place but the developers cannot view the Management Activity monitor. Developers are NOT granted the sysadmin role, and should not have that role.

What permissions need to be set for SQL Server 2005 to allow users to view the Management Activity monitor? They should not be allowed to take actions on the activities.

The Active Monitor tool requires VIEW SERVER STATE permission; this permission is required to select from DMVs such as sys.sysprocesses.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thank you so much! This did the trick.

Barb

|||

Does Management Studio Express support the Activity Monitor?

I've enabled VIEW SERVER STATE for admin and still can't find the Activity Monitor in the GUI.

Thanks

|||

Unfortunately I am not familiar with the different GUI tools available for SQL Express. I would recommend asking this question on the SQL Express forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=385&SiteID=1) instead.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Where do you set this: To view the Activity Monitor the user needs VIEW SERVER STATE permission on a SQL Server 2005 server.

|||

The easiest way is to just open a connection (i.e. using sqlcmd or Management Studio) using your sysadmin credentials and issue the following statement:

GRANT VIEW SERVER STATE TO <<login name>>

This will grant <<login_name>> VIEW ANY STATE permission.

-Raul Garcia

SDE/T

SQL Server Engine

Permissions in SQL Server 2005 to allow users to view the Management Activity monitor?

For SQL Server 2000 we have a user login mapped to msdb with database role membership of db_datareader and public checked. This seems to allow the developers to view the Management Activity monitor. For SQL Server 2005 the same mapping is in place but the developers cannot view the Management Activity monitor. Developers are NOT granted the sysadmin role, and should not have that role.

What permissions need to be set for SQL Server 2005 to allow users to view the Management Activity monitor? They should not be allowed to take actions on the activities.

The Active Monitor tool requires VIEW SERVER STATE permission; this permission is required to select from DMVs such as sys.sysprocesses.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thank you so much! This did the trick.

Barb

|||

Does Management Studio Express support the Activity Monitor?

I've enabled VIEW SERVER STATE for admin and still can't find the Activity Monitor in the GUI.

Thanks

|||

Unfortunately I am not familiar with the different GUI tools available for SQL Express. I would recommend asking this question on the SQL Express forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=385&SiteID=1) instead.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Where do you set this: To view the Activity Monitor the user needs VIEW SERVER STATE permission on a SQL Server 2005 server.

|||

The easiest way is to just open a connection (i.e. using sqlcmd or Management Studio) using your sysadmin credentials and issue the following statement:

GRANT VIEW SERVER STATE TO <<login name>>

This will grant <<login_name>> VIEW ANY STATE permission.

-Raul Garcia

SDE/T

SQL Server Engine

permissions for developers not working after 2005 upgrade

Prior to our move to 2005...permissions were granted to developers by adding them to the following fixed database roles...db_ddladmin, db_datareader, db_datawriter, and db_securityadmin. They created their objects using 'dbo' as the owner.

After upgrading to 2005, suddently they are having difficulty accessing their objects with this same security. Do they need permissions on the dbo schema?

Can you please elaborate on the access difficulty? Are you encountering errors and, if yes, what are those errors? It would help if you could give us an example of some action that used to work and now doesn't, and of what is the system response in this case.

Thanks
Laurentiu

|||

They are making a connection through Visual Studio using the MS OLE DB Provider for SQL Server with their domain account and receiving the following error...

SELECT permission denied on object 'Contact_Info', database 'GetLean', schema 'dbo'.

|||

Could you check what is the current execution context at the time when this error is obtained? You can use Profiler to figure out what is the current execution context.

There are two possibilities:

(1) current execution context is not a member of db_datareader, so you do not have SELECT permission.

(2) current execution context is explicitly denied SELECT permission on the Contact_Info table.

If you determine the current execution context, then you can check whether it's a member of db_datareader by looking at the sys.database_role_members catalog. You can check for the SELECT permission being denied by looking at the sys.database_permissions catalog.

Thanks
Laurentiu

|||

They are a member of the db_datareader, db_datawriter, db_ddladmin, and db_securityadmin roles. There are no permissions explicitly denied in this database.

Even though they have db_datareader, db_datawriter, db_ddladmin, and db_securityadmin rights....must I still assign them to the dbo schema?

|||

db_datareader grants select on the entire database, hence on the dbo schema as well (http://msdn2.microsoft.com/en-us/library/ms189612.aspx). You don't need to do a special permission grant for the dbo schema.

Can you try a little experiment? Create a test table in the dbo schema and then verify if those members of db_datareader can access it. Also, create a separate schema and a table in it and see if there is the same behavior for it as for the dbo schema.

Also, does this happen for all those developers or only for some? If some of them were added to db_denydatareader role, then that would prevent them from selecting from anything. Are they members of other roles than those four that you mentioned?

Thanks
Laurentiu

|||

It turned out not to be a DBA problem. Sorry, should have posted the resolution earlier.

It turned out to be a developer issue...a combination of failing to practice current standards and inexperience with Visual Studio. Aargh!

permissions for custom roles

I am on sql 2000 sp 4, windows 2003.
I am looking for a way to create custom database roles that are more
exacting than db_datareader, db_dbowner etc. What I need to do is to
create roles for the help desk personnel where they can view scheduled
jobs, but not edit them or create them. I also need to grant access to
edit DTS packages but not delete or create the. Does anyone have any
experience or good articles about this?
Thanks!
KristinaKristina,
Did you already get help through googlegroups? A couple of simple things on
SQL Server 2000.
Make those who should monitor jobs and schedules but not change them members
of the msdb TargetServersRole role. (Member of this role _cannot_ create new
jobs, so do not use this for someone who must also create jobs.)
Regarding DTS packages, I keep them stored on the file system, not in msdb.
Therefore, I control edit rights by giving the needed users Read access to
the DTS folders, but not Change rights.
RLF
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1184982108.759067.184370@.r34g2000hsd.googlegroups.com...
>I am on sql 2000 sp 4, windows 2003.
> I am looking for a way to create custom database roles that are more
> exacting than db_datareader, db_dbowner etc. What I need to do is to
> create roles for the help desk personnel where they can view scheduled
> jobs, but not edit them or create them. I also need to grant access to
> edit DTS packages but not delete or create the. Does anyone have any
> experience or good articles about this?
> Thanks!
> Kristina
>|||On Jul 23, 10:44 am, "Russell Fields" <russellfie...@.nomail.com>
wrote:
> Kristina,
> Did you already get help through googlegroups? A couple of simple things on
> SQL Server 2000.
> Make those who should monitor jobs and schedules but not change them members
> of the msdb TargetServersRole role. (Member of this role _cannot_ create new
> jobs, so do not use this for someone who must also create jobs.)
> Regarding DTS packages, I keep them stored on the file system, not in msdb.
> Therefore, I control edit rights by giving the needed users Read access to
> the DTS folders, but not Change rights.
> RLF"Kristina" <Kristina...@.gmail.com> wrote in message
> news:1184982108.759067.184370@.r34g2000hsd.googlegroups.com...
>
> >I am on sql 2000 sp 4, windows 2003.
> > I am looking for a way to create custom database roles that are more
> > exacting than db_datareader, db_dbowner etc. What I need to do is to
> > create roles for the help desk personnel where they can view scheduled
> > jobs, but not edit them or create them. I also need to grant access to
> > edit DTS packages but not delete or create the. Does anyone have any
> > experience or good articles about this?
> > Thanks!
> > Kristina- Hide quoted text -
> - Show quoted text -
Russell,
Thanks as always. This is the perfect advice I need! I have a crazy
new contract and I am the developer/DBA etc and don't have time to
research etc.
Kristina

permissions for custom roles

I am on sql 2000 sp 4, windows 2003.
I am looking for a way to create custom database roles that are more
exacting than db_datareader, db_dbowner etc. What I need to do is to
create roles for the help desk personnel where they can view scheduled
jobs, but not edit them or create them. I also need to grant access to
edit DTS packages but not delete or create the. Does anyone have any
experience or good articles about this?
Thanks!
Kristina
Kristina,
Did you already get help through googlegroups? A couple of simple things on
SQL Server 2000.
Make those who should monitor jobs and schedules but not change them members
of the msdb TargetServersRole role. (Member of this role _cannot_ create new
jobs, so do not use this for someone who must also create jobs.)
Regarding DTS packages, I keep them stored on the file system, not in msdb.
Therefore, I control edit rights by giving the needed users Read access to
the DTS folders, but not Change rights.
RLF
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1184982108.759067.184370@.r34g2000hsd.googlegr oups.com...
>I am on sql 2000 sp 4, windows 2003.
> I am looking for a way to create custom database roles that are more
> exacting than db_datareader, db_dbowner etc. What I need to do is to
> create roles for the help desk personnel where they can view scheduled
> jobs, but not edit them or create them. I also need to grant access to
> edit DTS packages but not delete or create the. Does anyone have any
> experience or good articles about this?
> Thanks!
> Kristina
>
|||On Jul 23, 10:44 am, "Russell Fields" <russellfie...@.nomail.com>
wrote:
> Kristina,
> Did you already get help through googlegroups? A couple of simple things on
> SQL Server 2000.
> Make those who should monitor jobs and schedules but not change them members
> of the msdb TargetServersRole role. (Member of this role _cannot_ create new
> jobs, so do not use this for someone who must also create jobs.)
> Regarding DTS packages, I keep them stored on the file system, not in msdb.
> Therefore, I control edit rights by giving the needed users Read access to
> the DTS folders, but not Change rights.
> RLF"Kristina" <Kristina...@.gmail.com> wrote in message
> news:1184982108.759067.184370@.r34g2000hsd.googlegr oups.com...
>
>
>
> - Show quoted text -
Russell,
Thanks as always. This is the perfect advice I need! I have a crazy
new contract and I am the developer/DBA etc and don't have time to
research etc.
Kristina

permissions for custom roles

I am on sql 2000 sp 4, windows 2003.
I am looking for a way to create custom database roles that are more
exacting than db_datareader, db_dbowner etc. What I need to do is to
create roles for the help desk personnel where they can view scheduled
jobs, but not edit them or create them. I also need to grant access to
edit DTS packages but not delete or create the. Does anyone have any
experience or good articles about this?
Thanks!
KristinaKristina,
Did you already get help through googlegroups? A couple of simple things on
SQL Server 2000.
Make those who should monitor jobs and schedules but not change them members
of the msdb TargetServersRole role. (Member of this role _cannot_ create new
jobs, so do not use this for someone who must also create jobs.)
Regarding DTS packages, I keep them stored on the file system, not in msdb.
Therefore, I control edit rights by giving the needed users Read access to
the DTS folders, but not Change rights.
RLF
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1184982108.759067.184370@.r34g2000hsd.googlegroups.com...
>I am on sql 2000 sp 4, windows 2003.
> I am looking for a way to create custom database roles that are more
> exacting than db_datareader, db_dbowner etc. What I need to do is to
> create roles for the help desk personnel where they can view scheduled
> jobs, but not edit them or create them. I also need to grant access to
> edit DTS packages but not delete or create the. Does anyone have any
> experience or good articles about this?
> Thanks!
> Kristina
>|||On Jul 23, 10:44 am, "Russell Fields" <russellfie...@.nomail.com>
wrote:
> Kristina,
> Did you already get help through googlegroups? A couple of simple things
on
> SQL Server 2000.
> Make those who should monitor jobs and schedules but not change them membe
rs
> of the msdb TargetServersRole role. (Member of this role _cannot_ create n
ew
> jobs, so do not use this for someone who must also create jobs.)
> Regarding DTS packages, I keep them stored on the file system, not in msdb
.
> Therefore, I control edit rights by giving the needed users Read access to
> the DTS folders, but not Change rights.
> RLF"Kristina" <Kristina...@.gmail.com> wrote in message
> news:1184982108.759067.184370@.r34g2000hsd.googlegroups.com...
>
>
>
>
> - Show quoted text -
Russell,
Thanks as always. This is the perfect advice I need! I have a crazy
new contract and I am the developer/DBA etc and don't have time to
research etc.
Kristina

Permission for a newly created user

I have created a login that belongs to both Public and
db_datareader database role to a specific database.
However, when I open the property of that newly created
user and select "List only objects with permissions for
this user". It is blank.
On the other hand, when I connect to QA with that user, I
am able to run SQL Script.
Is there anything wrong ? OR It is correct that we are
not able to see the access right in EM ?Peter
db_datareader is intended for SELECT all data from any users tables
If you want the user to not be able to modify any data in users tables make
him a member of db_denydatawriter
fixed database role
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:7c4801c5282d$0b0b3390$a601280a@.phx.gbl...
> I have created a login that belongs to both Public and
> db_datareader database role to a specific database.
> However, when I open the property of that newly created
> user and select "List only objects with permissions for
> this user". It is blank.
> On the other hand, when I connect to QA with that user, I
> am able to run SQL Script.
> Is there anything wrong ? OR It is correct that we are
> not able to see the access right in EM ?|||No...nothing is wrong. You will see the permissions
explicitly granted to the user when viewing the permissions
from Enterprise Manager, users. You won't see the
permissions the user has based on membership to the role.
-Sue
On Sun, 13 Mar 2005 16:30:37 -0800, "Peter"
<anonymous@.discussions.microsoft.com> wrote:
>I have created a login that belongs to both Public and
>db_datareader database role to a specific database.
>However, when I open the property of that newly created
>user and select "List only objects with permissions for
>this user". It is blank.
>On the other hand, when I connect to QA with that user, I
>am able to run SQL Script.
>Is there anything wrong ? OR It is correct that we are
>not able to see the access right in EM ?

Permission

Hi,
If I grant a user with the permission of db_datareader in EM, do I also
need to click on the Permission Button and check on every table/view in orde
r
for her to view them or the db_datareader means a user can view all
table/view by default?
Thanks
EdEd,
No. As BOL says "Can select all data from any user table in the database.".
Here is a script to prove it, assuming that your server has "SQL Server and
Windows" authentication.
Example:
-- DO NOT TEST IT IN A PRODUCTION SERVER
use master
go
execute sp_addlogin 'albert', 'b1r12-36', 'northwind'
go
use northwind
go
execute sp_grantdbaccess 'albert', 'albert'
go
execute sp_addrolemember 'db_datareader', 'albert'
go
select
a.*
from
openrowset('sqloledb', 'server=yourServer;uid=albert;pwd=b1r12-36',
northwind.dbo.orders) as a
go
execute sp_droprolemember 'db_datareader', 'albert'
go
execute sp_revokedbaccess @.name_in_db = 'albert'
go
use master
go
declare @.spid int
declare @.s varchar(50)
select
@.spid = spid
from
sysprocesses
where
loginame = 'albert'
and dbid = db_id('northwind')
if @.spid is not null
begin
set @.s = 'kill ' + ltrim(@.spid)
exec(@.s)
end
go
execute sp_droplogin @.loginame = 'albert'
go
AMB
"Ed" wrote:

> Hi,
> If I grant a user with the permission of db_datareader in EM, do I also
> need to click on the Permission Button and check on every table/view in or
der
> for her to view them or the db_datareader means a user can view all
> table/view by default?
> Thanks
> Ed