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 running sp_Start_Job and xp_sqlagent_enum_jobs

Anybody know what permissions I need to give a user so
that they can execute msdb.dbo.sp_start_job and
xp_sqlagent_enum_jobs? I don't want to give them Admin
access..The user needs to own the job if you don't want them to be a
sysadmin.
-Sue
On Wed, 3 Sep 2003 08:21:05 -0700, "Rich"
<rslack@.asda.co.uk> wrote:
>Anybody know what permissions I need to give a user so
>that they can execute msdb.dbo.sp_start_job and
>xp_sqlagent_enum_jobs? I don't want to give them Admin
>access..

Permission Questions After running DTS TransferLogins

I moved user DBs to a new SQL 2000sp4 server running win2003r2, also moved
the MSDB, not the Master, then I used DTS to transfer user logins to the new
SQL box. Now I have problems changing some login permissions. When I try
to add a database to a login I get this error > "error 15081: membership of
the public role cannot be changed" AND there I am unable to make any changes
to the public ROLE. Can some one tell me what I am missing ' Thanks you..Hi
Using the DTS package to transfer logins will not re-create the original
SIDs therefore you would need to resolve orphanded users!
sp_change_users_login 'report' would show any orphaned users, see
http://support.microsoft.com/kb/274188/ and
http://support.microsoft.com/kb/240872 for more.
John
"WANNABE" wrote:
> I moved user DBs to a new SQL 2000sp4 server running win2003r2, also moved
> the MSDB, not the Master, then I used DTS to transfer user logins to the new
> SQL box. Now I have problems changing some login permissions. When I try
> to add a database to a login I get this error > "error 15081: membership of
> the public role cannot be changed" AND there I am unable to make any changes
> to the public ROLE. Can some one tell me what I am missing ' Thanks you..
>
>

Permission Questions After running DTS TransferLogins

I moved user DBs to a new SQL 2000sp4 server running win2003r2, also moved
the MSDB, not the Master, then I used DTS to transfer user logins to the new
SQL box. Now I have problems changing some login permissions. When I try
to add a database to a login I get this error > "error 15081: membership of
the public role cannot be changed" AND there I am unable to make any changes
to the public ROLE. Can some one tell me what I am missing ' Thanks you..Hi
Using the DTS package to transfer logins will not re-create the original
SIDs therefore you would need to resolve orphanded users!
sp_change_users_login 'report' would show any orphaned users, see
http://support.microsoft.com/kb/274188/ and
http://support.microsoft.com/kb/240872 for more.
John
"WANNABE" wrote:

> I moved user DBs to a new SQL 2000sp4 server running win2003r2, also moved
> the MSDB, not the Master, then I used DTS to transfer user logins to the n
ew
> SQL box. Now I have problems changing some login permissions. When I try
> to add a database to a login I get this error > "error 15081: membership o
f
> the public role cannot be changed" AND there I am unable to make any chang
es
> to the public ROLE. Can some one tell me what I am missing ' Thanks you
.
>
>

permission question

Hi,

I granted a domain user login:
--read only to our production db.
--db_owner to msdb.

I want this login to be able to create jobs & dts packages.
he's able to create dts packages, but when he tries to create a new job, in the db dropdown menu (steps tab) he can't see the production db. He only sees msdb, master & temp... he needs to see the production db, so that he can create a job.

please help!
MeeraHowdy

You have done the right thing allowing access to MSDB. He should be able to create DTS packages anyway ( anyone can ) .

With his login, what is his default database? Also, has his login ( not a group etc ) been actually granted access to the production database?

I say this as the Public group in a database can be emptied in the database, thereby not everyone on the server is granted access automatically to the database.

Cheers

SG|||Thanks for replying!

This guy belongs to a domain user group called dbCustomReports. So, when he registers his servers in Enterprise manager using the runas command, he'll be registered as Domain/dbCustomReports.

His default db is the production db.

The login in SQL server is actually Domain/dbCustomReports. That login has db_owner permissions on msdb & production.

Which login should I grant permissions to? I didn't follow that part of your question... (he doesn't have any individual sql server login)

Any help is appreciated.
Thanks,
Meera|||you should grant the domain/dbCustomReports access to you production database. Setting a default database does not grant access to that database. You have granted permissions to the group to which the user belongs. Be aware that anyone else belonging to that group has the same privileges in the database.|||That account has been granted db_owner priviliges to the production db & then I set the default db :)
Meera|||can the user see the production database in the enterprise manager or also only msdb, master and temp?|||in EM, the user can see the production db and all the other db (of course when he tries to see the tables, it won't let him.)

Thanks,
Meera|||How did you set up the permissions for this user? Are there any other privileges or database roles granted?|||database called CR -- db_owner
msdb -- db_owner
database called Prod -- read only

Meera|||I think you should only give read only to the first one too

Paulo

Originally posted by meeraarvind
database called CR -- db_owner
msdb -- db_owner
database called Prod -- read only

Meera

Permission for Jobs

I have a user who needs to create jobs and also manage some already existing
jobs owned by dbo. I have added her Windows domain id in MSDB data which will
allow her to create and manage her own jobs. I was trying to add her to the
TargetServersRole but this role has been denied access to stored procedures
e.g. sp_update_job in MSDB database. How can I achieve this. Please advise.
Thanks..Fraz
Only members of the sysadmin server role can alter, delete and start jobs
owned by another user. This is hardcoded into the respective system stored
procedures (sp_update_job, sp_start_job etc), so if you want you can change
these stored procedures in msdb to take out the check to see if the user is
a member of the sysadmin role or owns the job. This is unsupported though.
Jacco Schalkwijk
SQL Server MVP
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:43343195-6BEC-4AC6-9178-B94B92CE57D5@.microsoft.com...
>I have a user who needs to create jobs and also manage some already
>existing
> jobs owned by dbo. I have added her Windows domain id in MSDB data which
> will
> allow her to create and manage her own jobs. I was trying to add her to
> the
> TargetServersRole but this role has been denied access to stored
> procedures
> e.g. sp_update_job in MSDB database. How can I achieve this. Please
> advise.
> Thanks..Fraz

Permission for Jobs

I have a user who needs to create jobs and also manage some already existing
jobs owned by dbo. I have added her Windows domain id in MSDB data which will
allow her to create and manage her own jobs. I was trying to add her to the
TargetServersRole but this role has been denied access to stored procedures
e.g. sp_update_job in MSDB database. How can I achieve this. Please advise.
Thanks..FrazOnly members of the sysadmin server role can alter, delete and start jobs
owned by another user. This is hardcoded into the respective system stored
procedures (sp_update_job, sp_start_job etc), so if you want you can change
these stored procedures in msdb to take out the check to see if the user is
a member of the sysadmin role or owns the job. This is unsupported though.
--
Jacco Schalkwijk
SQL Server MVP
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:43343195-6BEC-4AC6-9178-B94B92CE57D5@.microsoft.com...
>I have a user who needs to create jobs and also manage some already
>existing
> jobs owned by dbo. I have added her Windows domain id in MSDB data which
> will
> allow her to create and manage her own jobs. I was trying to add her to
> the
> TargetServersRole but this role has been denied access to stored
> procedures
> e.g. sp_update_job in MSDB database. How can I achieve this. Please
> advise.
> Thanks..Fraz

Permission for Jobs

I have a user who needs to create jobs and also manage some already existing
jobs owned by dbo. I have added her Windows domain id in MSDB data which wil
l
allow her to create and manage her own jobs. I was trying to add her to the
TargetServersRole but this role has been denied access to stored procedures
e.g. sp_update_job in MSDB database. How can I achieve this. Please advise.
Thanks..FrazOnly members of the sysadmin server role can alter, delete and start jobs
owned by another user. This is hardcoded into the respective system stored
procedures (sp_update_job, sp_start_job etc), so if you want you can change
these stored procedures in msdb to take out the check to see if the user is
a member of the sysadmin role or owns the job. This is unsupported though.
Jacco Schalkwijk
SQL Server MVP
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:43343195-6BEC-4AC6-9178-B94B92CE57D5@.microsoft.com...
>I have a user who needs to create jobs and also manage some already
>existing
> jobs owned by dbo. I have added her Windows domain id in MSDB data which
> will
> allow her to create and manage her own jobs. I was trying to add her to
> the
> TargetServersRole but this role has been denied access to stored
> procedures
> e.g. sp_update_job in MSDB database. How can I achieve this. Please
> advise.
> Thanks..Fraz