Am I doing this right?

I have a new user that will eventually be another admin on our SQL Server 20
0
installation. I want to give him permission to stop and Restart Scheduled
jobs, yet not have permission to insert/update or delete in specific
databases until he gets more experience. The only predefined server role
that allows access to scheduled jobs is SystemAdministrator, but that also
gives permissions everywhere to everything.
Will creating a User in each database I want to protect based on his login,
and then selecting db_denydatawriter do what I want to do, or will the also
selected SystemAdministrator priviledge override this altogether?A deny for a sysadmin won't do anything. Members of the
sysadmin role bypass security checks - sysadmins are able to
do anything on the server and in any databases.
-Sue
On Thu, 18 May 2006 11:41:03 -0700, Steve
<Steve@.discussions.microsoft.com> wrote:

>I have a new user that will eventually be another admin on our SQL Server 2
00
>installation. I want to give him permission to stop and Restart Scheduled
>jobs, yet not have permission to insert/update or delete in specific
>databases until he gets more experience. The only predefined server role
>that allows access to scheduled jobs is SystemAdministrator, but that also
>gives permissions everywhere to everything.
>Will creating a User in each database I want to protect based on his login,
>and then selecting db_denydatawriter do what I want to do, or will the also
>selected SystemAdministrator priviledge override this altogether?|||Thanks for the reply. So then how in the world can I create a login that
allows the user to access, start and stop SQL Server Agent scheduled jobs an
d
do liuttle or nothing else? I am stumped on this and it seems like it
*ought* to be easy!
Steve
"Sue Hoegemeier" wrote:

> A deny for a sysadmin won't do anything. Members of the
> sysadmin role bypass security checks - sysadmins are able to
> do anything on the server and in any databases.
> -Sue
> On Thu, 18 May 2006 11:41:03 -0700, Steve
> <Steve@.discussions.microsoft.com> wrote:
>
>|||There just isn't that level of granularity with security
under SQL Server 2000. That changes in SQL Server 2005
though.
The only way to get close is by using an undocumented role
in msdb - TargetServer role. However, the permissions for
this role changes depending on the service pack level and
you'd have to be running at SP 2 or lower to get close to
what you are asking for.
-Sue
On Fri, 19 May 2006 13:11:03 -0700, Steve
<Steve@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for the reply. So then how in the world can I create a login that
>allows the user to access, start and stop SQL Server Agent scheduled jobs a
nd
>do liuttle or nothing else? I am stumped on this and it seems like it
>*ought* to be easy!
>Steve
>"Sue Hoegemeier" wrote:
>|||Hi Sue/Steve
I feel with Sp3 & higher we can achieve these by modifying default Security
Permissions asigned to TargetServer Role. But only quetion is what Security
Risk we have when we enable this option .i.e. TargetServer Role.
I am in scenario where we want to view SQL job history running on more tahn
100 Servers & we don;t have master server scenario. Can you suggest somethin
g
on this?
Regards
--
Rahul
"Sue Hoegemeier" wrote:

> There just isn't that level of granularity with security
> under SQL Server 2000. That changes in SQL Server 2005
> though.
> The only way to get close is by using an undocumented role
> in msdb - TargetServer role. However, the permissions for
> this role changes depending on the service pack level and
> you'd have to be running at SP 2 or lower to get close to
> what you are asking for.
> -Sue
> On Fri, 19 May 2006 13:11:03 -0700, Steve
> <Steve@.discussions.microsoft.com> wrote:
>
>|||No...I wouldn't suggest that and don't think it would work.
Additionally using TargetServer role is undocumented as I
already posted.
I'd look at writing your own application, front end to
manage you needs.
-Sue
On Tue, 25 Jul 2006 21:12:02 -0700, rahulpt
<rahulpt@.discussions.microsoft.com> wrote:

>Hi Sue/Steve
>I feel with Sp3 & higher we can achieve these by modifying default Security
>Permissions asigned to TargetServer Role. But only quetion is what Security
>Risk we have when we enable this option .i.e. TargetServer Role.
>I am in scenario where we want to view SQL job history running on more tahn
>100 Servers & we don;t have master server scenario. Can you suggest somethi
ng
>on this?
>Regards

alternative to Outlook/ Exchange?

Is there anything other than Outlook/ Exchange that can be used in order to
set up SQL Mail/ Alerts? My Network Admin is hoping for some type of SMTP
Relay type of deal.
Check the following extended stored procedure for SMTP mail:
http://www.sqldev.net/xp/xpsmtp.htm
-Sue
On Tue, 5 Apr 2005 16:30:42 -0700, "ChrisR"
<noemail@.bla.com> wrote:

>Is there anything other than Outlook/ Exchange that can be used in order to
>set up SQL Mail/ Alerts? My Network Admin is hoping for some type of SMTP
>Relay type of deal.
>
|||Thanks Sue. Do you know if this is supported by MS?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:b1d6519p7d7ooer6vtumjjab67g5sijker@.4ax.com...
> Check the following extended stored procedure for SMTP mail:
> http://www.sqldev.net/xp/xpsmtp.htm
> -Sue
> On Tue, 5 Apr 2005 16:30:42 -0700, "ChrisR"
> <noemail@.bla.com> wrote:
>
|||Depends on what you mean - Microsoft doesn't support third
party utilities but adding the extended stored procedure and
using it doesn't leave you with an unsupported installation
of SQL Server.
It's stable to use...many people use it. I've been using it
for a few years. I think it's a improvement over using SQL
Mail, Agent Mail, MAPI, etc.
-Sue
On Wed, 6 Apr 2005 07:54:08 -0700, "ChrisR"
<noemail@.bla.com> wrote:

>Thanks Sue. Do you know if this is supported by MS?
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:b1d6519p7d7ooer6vtumjjab67g5sijker@.4ax.com.. .
>

alternative to Outlook/ Exchange?

Is there anything other than Outlook/ Exchange that can be used in order to
set up SQL Mail/ Alerts? My Network Admin is hoping for some type of SMTP
Relay type of deal.Check the following extended stored procedure for SMTP mail:
http://www.sqldev.net/xp/xpsmtp.htm
-Sue
On Tue, 5 Apr 2005 16:30:42 -0700, "ChrisR"
<noemail@.bla.com> wrote:
>Is there anything other than Outlook/ Exchange that can be used in order to
>set up SQL Mail/ Alerts? My Network Admin is hoping for some type of SMTP
>Relay type of deal.
>|||Thanks Sue. Do you know if this is supported by MS?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:b1d6519p7d7ooer6vtumjjab67g5sijker@.4ax.com...
> Check the following extended stored procedure for SMTP mail:
> http://www.sqldev.net/xp/xpsmtp.htm
> -Sue
> On Tue, 5 Apr 2005 16:30:42 -0700, "ChrisR"
> <noemail@.bla.com> wrote:
>>Is there anything other than Outlook/ Exchange that can be used in order
>>to
>>set up SQL Mail/ Alerts? My Network Admin is hoping for some type of SMTP
>>Relay type of deal.
>|||Depends on what you mean - Microsoft doesn't support third
party utilities but adding the extended stored procedure and
using it doesn't leave you with an unsupported installation
of SQL Server.
It's stable to use...many people use it. I've been using it
for a few years. I think it's a improvement over using SQL
Mail, Agent Mail, MAPI, etc.
-Sue
On Wed, 6 Apr 2005 07:54:08 -0700, "ChrisR"
<noemail@.bla.com> wrote:
>Thanks Sue. Do you know if this is supported by MS?
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:b1d6519p7d7ooer6vtumjjab67g5sijker@.4ax.com...
>> Check the following extended stored procedure for SMTP mail:
>> http://www.sqldev.net/xp/xpsmtp.htm
>> -Sue
>> On Tue, 5 Apr 2005 16:30:42 -0700, "ChrisR"
>> <noemail@.bla.com> wrote:
>>Is there anything other than Outlook/ Exchange that can be used in order
>>to
>>set up SQL Mail/ Alerts? My Network Admin is hoping for some type of SMTP
>>Relay type of deal.
>>
>

alternative to Outlook/ Exchange?

Is there anything other than Outlook/ Exchange that can be used in order to
set up SQL Mail/ Alerts? My Network Admin is hoping for some type of SMTP
Relay type of deal.Check the following extended stored procedure for SMTP mail:
http://www.sqldev.net/xp/xpsmtp.htm
-Sue
On Tue, 5 Apr 2005 16:30:42 -0700, "ChrisR"
<noemail@.bla.com> wrote:

>Is there anything other than Outlook/ Exchange that can be used in order to
>set up SQL Mail/ Alerts? My Network Admin is hoping for some type of SMTP
>Relay type of deal.
>|||Thanks Sue. Do you know if this is supported by MS?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:b1d6519p7d7ooer6vtumjjab67g5sijker@.
4ax.com...
> Check the following extended stored procedure for SMTP mail:
> http://www.sqldev.net/xp/xpsmtp.htm
> -Sue
> On Tue, 5 Apr 2005 16:30:42 -0700, "ChrisR"
> <noemail@.bla.com> wrote:
>
>|||Depends on what you mean - Microsoft doesn't support third
party utilities but adding the extended stored procedure and
using it doesn't leave you with an unsupported installation
of SQL Server.
It's stable to use...many people use it. I've been using it
for a few years. I think it's a improvement over using SQL
Mail, Agent Mail, MAPI, etc.
-Sue
On Wed, 6 Apr 2005 07:54:08 -0700, "ChrisR"
<noemail@.bla.com> wrote:

>Thanks Sue. Do you know if this is supported by MS?
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:b1d6519p7d7ooer6vtumjjab67g5sijker@.
4ax.com...
>

Alternative to Account Delegation?

Hello,
I want to run distributed queries via a linked server using a windows
autheniticated login. Talking with our AD/network admin about the steps in
http://msdn.microsoft.com/library/d...>
ity_2gmm.asp
they have a problem with setting up account delegation for service account
and sql server computer, mainly because this is a new precedent and they
don't want to go there unless absolutely necessary.
Are there any alternatives to doing the above that would enable distributed
queries using windows authenicated logins?
Thanks,
DanHi
No. The way the OS has to impersonate and delegate security across machine
boundaries requires this. If there was an easier way, we would all be using
it.
Your admins will need to get used to it. As security gets tightened inside
the OS, you will need to use it more and more.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dan H" wrote:

> Hello,
> I want to run distributed queries via a linked server using a windows
> autheniticated login. Talking with our AD/network admin about the steps i
n
> http://msdn.microsoft.com/library/d...
urity_2gmm.asp
> they have a problem with setting up account delegation for service account
> and sql server computer, mainly because this is a new precedent and they
> don't want to go there unless absolutely necessary.
> Are there any alternatives to doing the above that would enable distribute
d
> queries using windows authenicated logins?
> Thanks,
> Dan