Permissions to allow updating stored procedures

Has anyone worked out what permissions are required such
that a given database user can create, update and/or
delete stored procedures, but not do the same to
tables/views ?CREATE PROCEDURE permission is needed to modify SPs. CREATE VIEW is a
separate permission.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Jim Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message
news:fd1001c40d60$e1f1acb0$a301280a@.phx.gbl...
> Has anyone worked out what permissions are required such
> that a given database user can create, update and/or
> delete stored procedures, but not do the same to
> tables/views ?

Permissions required to run DTS packages and jobs

What SQL Server permission(s) are required to run DTS packages and jobs in
SQL Server 2000 and SQL Server 2005?
Thank you.
--
MikeMike
SQL Server 2000
1) You are the owner of the package
2) Jobs are ran under account that SQL Server Agent are runing under.
3) Lookuop TargerServerRole in the BOL
SQL Server 2005
1) I haven't played to much with this issue. If I remember well you can
create a proxy account to run jobs
with an acount that is not a member of sysadmin server role.
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:57249ED9-D4A8-4733-BF06-F903C65CCD83@.microsoft.com...
> What SQL Server permission(s) are required to run DTS packages and jobs in
> SQL Server 2000 and SQL Server 2005?
> Thank you.
> --
> Mikesql

Permissions required to restore a database

Hi,
I need to restore a database to a new server. What are
the minimum permissions will I need to perform this task?
Thanks,
Diana
This is from Books Online:
Permissions
If the database being restored does not exist, the user must have CREATE
DATABASE permissions to be able to execute RESTORE. If the database exists,
RESTORE permissions default to members of the sysadmin and dbcreator fixed
server roles and the owner (dbo) of the database.
RESTORE permissions are given to roles in which membership information is
always readily available to the server. Because fixed database role
membership can be checked only when the database is accessible and
undamaged, which is not always the case when RESTORE is executed, members of
the db_owner fixed database role do not have RESTORE permissions.
Keith
"Diana" <anonymous@.discussions.microsoft.com> wrote in message
news:154201c4fd89$92d7a750$a501280a@.phx.gbl...
> Hi,
> I need to restore a database to a new server. What are
> the minimum permissions will I need to perform this task?
> Thanks,
> Diana
>

Permissions required for sp_databases

This may be the wrong forum for this - if so, please direct me to the appropriate forum.

I've recently upgraded to MSSQL 2005 and I'm trying to execute the sp_databases stored procedure, but I'm having what looks like permissions problems. I can execute the stored procedure, but it doesn't return any results. As far as I can tell, the problem is that my userid can't see any rows in sys.master_files (I can select from the view, it just doesn't return any rows).

I've tried any number of things to get this to work, but the only thing that's been successful is to change my userid's database role membership to db_owner. In the long run, this isn't really a viable solution for me, since my userid should only really have read(select) access to the database in question.

I'd appreciate any pointers that you can give me - I figure I'm doing something stupid, but haven't been able to figure out what, yet.

Thanks,
Jeff

If you grant VIEW ANY DEFINITION, you will be able to see this information for the databases that you can access. However, VIEW ANY DEFINITION will open other catalogs as well.

I suggest to write a wrapper procedure over sp_databases that is signed with a certificate and grant that certificate the VIEW ANY DEFINITION permission, then grant EXECUTE permission on the procedure to the principal that needs this functionality.

Thanks
Laurentiu

|||That seems to work, thanks!

Is there any way to grant permission for an individual database? My user is really only interested in the sp_databases row for a single database. The others are just ignored, so they could be dropped out of the result set with no loss of functionality (and that might limit the permissions that need to be granted).

Jeff
|||

You can use a signed procedure to filter the results and only display the row for a certain database. You cannot do this only using permissions.

Thanks
Laurentiu

|||So if a procedure is created as dbo a user who has readonly access to the db in which it was created wouldn't be able to query sysobjects or use sp_helptext to view procedure information? In 2000 the user would be able to see the procedure but this is not so in 2005.

Permissions required for sp_databases

This may be the wrong forum for this - if so, please direct me to the appropriate forum.

I've

recently upgraded to MSSQL 2005 and I'm trying to execute the

sp_databases stored procedure, but I'm having what looks like

permissions problems. I can execute the stored procedure, but it

doesn't return any results. As far as I can tell, the problem is that

my userid can't see any rows in sys.master_files (I can select from the view, it just doesn't return any rows).

I've tried any

number of things to get this to work, but the only thing that's been

successful is to change my userid's database role membership to

db_owner. In the long run, this isn't really a viable solution for me,

since my userid should only really have read(select) access to the

database in question.

I'd appreciate any pointers that you can

give me - I figure I'm doing something stupid, but haven't been able to

figure out what, yet.

Thanks,
Jeff

If you grant VIEW ANY DEFINITION, you will be able to see this information for the databases that you can access. However, VIEW ANY DEFINITION will open other catalogs as well.

I suggest to write a wrapper procedure over sp_databases that is signed with a certificate and grant that certificate the VIEW ANY DEFINITION permission, then grant EXECUTE permission on the procedure to the principal that needs this functionality.

Thanks
Laurentiu

|||That seems to work, thanks!

Is there any way to grant permission for an individual database? My user is really only interested in the sp_databases row for a single database. The others are just ignored, so they could be dropped out of the result set with no loss of functionality (and that might limit the permissions that need to be granted).

Jeff|||

You can use a signed procedure to filter the results and only display the row for a certain database. You cannot do this only using permissions.

Thanks
Laurentiu

|||So if a procedure is created as dbo a user who has readonly access to the db in which it was created wouldn't be able to query sysobjects or use sp_helptext to view procedure information? In 2000 the user would be able to see the procedure but this is not so in 2005.

permissions required for executing CDOSys stored procedures

Hi there
I'm doing some analysis on the database applications in my organisation
before migrating the databases to a new server. One of these makes use of
CDOSys objects for sending mail, instead of SQL Mail. There are a number of
stored procedures within the database that call the sp_OACreate &
sp_OASetProperty. Apparently only members of the sysadmin role can execute
these stored procedures, however, the sql login for this application is not
a
sysadmin! I thought perhaps there was a mistake in books online but i've
looked on google and the permission requirements are the same - must be
sysadmin. Any ideas how it still manages to function without these rights?
Thanks in advance!Neile,
Actually, it is possible to GRANT EXECUTE ON sp_OACreate TO
SomeOtherPrincipal.
RLF
"Neile Bermudes" <NB@.community.nospam> wrote in message
news:2048E482-F053-4BFA-92F4-D77C01A2FB54@.microsoft.com...
> Hi there
> I'm doing some analysis on the database applications in my organisation
> before migrating the databases to a new server. One of these makes use of
> CDOSys objects for sending mail, instead of SQL Mail. There are a number
> of
> stored procedures within the database that call the sp_OACreate &
> sp_OASetProperty. Apparently only members of the sysadmin role can execute
> these stored procedures, however, the sql login for this application is
> not a
> sysadmin! I thought perhaps there was a mistake in books online but i've
> looked on google and the permission requirements are the same - must be
> sysadmin. Any ideas how it still manages to function without these rights?
> Thanks in advance!|||Thanks for the response. I had a look at the permissions on the stored
procedures and could see none granted on the login in question... bizarre.
I
assume the way to check this is by going into the procedure properties scree
n
and clicking on the permissions tab? Is there another view somewhere else'
thx
"Russell Fields" wrote:

> Neile,
> Actually, it is possible to GRANT EXECUTE ON sp_OACreate TO
> SomeOtherPrincipal.
> RLF
> "Neile Bermudes" <NB@.community.nospam> wrote in message
> news:2048E482-F053-4BFA-92F4-D77C01A2FB54@.microsoft.com...
>
>|||Neile,
I usually use sp_helprotect to investigate these things. E.g.
sp_helprotect @.name='sp_OACreate'
I don't know what version of SQL Server you are using, but in 2005 the
properties of the sp_OACreate extended stored procedure has a permissions
page that shows this also, at least in SP2. With SQL Server 2000 Enterprise
Manager there is a permissions button on the properties form that serves a
similar purpose.
RLF
"Neile Bermudes" <NB@.community.nospam> wrote in message
news:850ABA77-4CBA-4788-99A4-E11B4530B044@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. I had a look at the permissions on the stored
> procedures and could see none granted on the login in question...
> bizarre. I
> assume the way to check this is by going into the procedure properties
> screen
> and clicking on the permissions tab? Is there another view somewhere
> else'
> thx
> "Russell Fields" wrote:
>|||Hi Russell,
Thanks for this. It's a sql 2000 box and i had checked the permission
properties page as you suggested below but it was blank. So it appears that
the these stored procedures are being executed by a user who is not a
sysadmin and who has not been granted specific execute permissions on the
procedures.
Is this possible'
Thanks
"Russell Fields" wrote:

> Neile,
> I usually use sp_helprotect to investigate these things. E.g.
> sp_helprotect @.name='sp_OACreate'
> I don't know what version of SQL Server you are using, but in 2005 the
> properties of the sp_OACreate extended stored procedure has a permissions
> page that shows this also, at least in SP2. With SQL Server 2000 Enterpri
se
> Manager there is a permissions button on the properties form that serves a
> similar purpose.
> RLF
> "Neile Bermudes" <NB@.community.nospam> wrote in message
> news:850ABA77-4CBA-4788-99A4-E11B4530B044@.microsoft.com...
>
>|||Neile,
I don't know what to say about a blank properties page except, "I don't
know." Of course, the sp_OA procedures can be granted to public, which
would give everybody rights, but would also result in public having a green
check box next to it.
What was the response to: sp_helprotect @.name='sp_OACreate'
Another possibility is that the user is also in the local administrators
group of the server. If so, he is by default member of the sysadmin server
role on the SQL Server database unless steps are taken to prevent that. For
example, as described in http://support.microsoft.com/kb/263712/.
RLF
"Neile Bermudes" <NB@.community.nospam> wrote in message
news:77B05D1F-5599-441B-89CA-B73940426480@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> Thanks for this. It's a sql 2000 box and i had checked the permission
> properties page as you suggested below but it was blank. So it appears
> that
> the these stored procedures are being executed by a user who is not a
> sysadmin and who has not been granted specific execute permissions on the
> procedures.
> Is this possible'
> Thanks
> "Russell Fields" wrote:
>|||Oh, and if the user is db_owner in master he also can execute sp_OA
procedures.
The BOL does not say this is possible, but I just tested it and it does work
on SQL 2000 and 2005. (So is this an 'undocumented' feature or an error in
documentation? I have reported it as an error.)
RLF
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OdHHkzqrHHA.3492@.TK2MSFTNGP02.phx.gbl...
> Neile,
> I don't know what to say about a blank properties page except, "I don't
> know." Of course, the sp_OA procedures can be granted to public, which
> would give everybody rights, but would also result in public having a
> green check box next to it.
> What was the response to: sp_helprotect @.name='sp_OACreate'
> Another possibility is that the user is also in the local administrators
> group of the server. If so, he is by default member of the sysadmin
> server role on the SQL Server database unless steps are taken to prevent
> that. For example, as described in
> http://support.microsoft.com/kb/263712/.
> RLF
> "Neile Bermudes" <NB@.community.nospam> wrote in message
> news:77B05D1F-5599-441B-89CA-B73940426480@.microsoft.com...
>|||Perhaps the cross-database chaining (a.k.a. db_chaining) database option is
turned on. In that case, execute permissions on indirectly referenced
objects in other databases are not needed as long as the ownership chain is
unbroken. Users need execute permissions on only the user stored procedure.
Note that the user database must be owned by 'sa' to maintain an unbroken
chain to master database objects. It is important that only sysadmin users
be allowed to create dbo-owned objects in this scenario in order to prevent
elevation of privileges.
Hope this helps.
Dan Guzman
SQL Server MVP
"Neile Bermudes" <NB@.community.nospam> wrote in message
news:2048E482-F053-4BFA-92F4-D77C01A2FB54@.microsoft.com...
> Hi there
> I'm doing some analysis on the database applications in my organisation
> before migrating the databases to a new server. One of these makes use of
> CDOSys objects for sending mail, instead of SQL Mail. There are a number
> of
> stored procedures within the database that call the sp_OACreate &
> sp_OASetProperty. Apparently only members of the sysadmin role can execute
> these stored procedures, however, the sql login for this application is
> not a
> sysadmin! I thought perhaps there was a mistake in books online but i've
> looked on google and the permission requirements are the same - must be
> sysadmin. Any ideas how it still manages to function without these rights?
> Thanks in advance!|||Dan,
Thanks for that reminder. We broke our ownership chains many years ago,
well before SQL 2000 SP3, by giving each database a different owner. It had
faded from memory.
RLF
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:48E04539-9C60-4265-94CD-19AEDFCA6443@.microsoft.com...
> Perhaps the cross-database chaining (a.k.a. db_chaining) database option
> is turned on. In that case, execute permissions on indirectly referenced
> objects in other databases are not needed as long as the ownership chain
> is unbroken. Users need execute permissions on only the user stored
> procedure.
> Note that the user database must be owned by 'sa' to maintain an unbroken
> chain to master database objects. It is important that only sysadmin
> users be allowed to create dbo-owned objects in this scenario in order to
> prevent elevation of privileges.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Neile Bermudes" <NB@.community.nospam> wrote in message
> news:2048E482-F053-4BFA-92F4-D77C01A2FB54@.microsoft.com...
>|||Hi Russell
Thanks for all the tips - this is all useful stuff to know. I discovered
that the stored procedure in the database which in turn calls the CDOSys
stored procedures is executed via a SQL job - and thus in the context of the
job owner, which is sa. So that explains it!!
But thanks for your suggestions and thanks to Dan for the info about cross
db chaining - all good to know.
Cheers
Neile
"Russell Fields" wrote:

> Dan,
> Thanks for that reminder. We broke our ownership chains many years ago,
> well before SQL 2000 SP3, by giving each database a different owner. It h
ad
> faded from memory.
> RLF
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:48E04539-9C60-4265-94CD-19AEDFCA6443@.microsoft.com...
>
>

permissions problems creating a linked server

I am trying to create a linked server in the management studio and am getting an error

"A required operation could not be completed. You must be a member of the sysadmin role to perform this operation"

I have tried giving the user rights via

GRANT ALTER ANY LINKED SERVER TO [DOM\user]

as well as adding them to the setupadmin group. No luck.

I can add the linked server via sp_addlinkedserver as the user.

Any ideas?After searching some more I came across this.

http://msdn2.microsoft.com/en-us/library/aa560998.aspx

Which states you must be a member of the sysadmin role to create a linked server to do this via the management tools. Any idea why?

When I script the GUI all of the SP's it calls are available to logins with the setupadmin role or who have been granted access to alter any linked server.

Permission/role needed to both modify and execute jobs

What SQL Server permission(s) are required to both modify and execute jobs in
SQL Server 2000 and SQL Server 2005?
Thank you.
--
MikeMike
You posted this in microsoft.public.sqlserver.security on 6 Jan, and
microsoft.public.sqlserver.dts 8 Jan Uri and Allan have replied to this.
Please do not muti-post http://www.aspfaq.com/etiquette.asp?id=5003
John
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:1767DE69-A68F-44AD-A821-924352B87079@.microsoft.com...
> What SQL Server permission(s) are required to both modify and execute jobs
> in
> SQL Server 2000 and SQL Server 2005?
> Thank you.
> --
> Mike|||I don 't see an answer for SQL Server 2005 (please see below). Any help
would be appreciated. Thank you.
SQL Server 2005
1) I haven't played to much with this issue. If I remember well you can
create a proxy account to run jobs
with an acount that is not a member of sysadmin server role.
--
Mike
"John Bell" wrote:
> Mike
> You posted this in microsoft.public.sqlserver.security on 6 Jan, and
> microsoft.public.sqlserver.dts 8 Jan Uri and Allan have replied to this.
> Please do not muti-post http://www.aspfaq.com/etiquette.asp?id=5003
> John
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:1767DE69-A68F-44AD-A821-924352B87079@.microsoft.com...
> > What SQL Server permission(s) are required to both modify and execute jobs
> > in
> > SQL Server 2000 and SQL Server 2005?
> >
> > Thank you.
> > --
> > Mike
>
>|||Additionally, if you would have read my new questions, you would have noticed
that they had changed from just run, to both modify and run/execute.
Thank you.
--
Mike
"John Bell" wrote:
> Mike
> You posted this in microsoft.public.sqlserver.security on 6 Jan, and
> microsoft.public.sqlserver.dts 8 Jan Uri and Allan have replied to this.
> Please do not muti-post http://www.aspfaq.com/etiquette.asp?id=5003
> John
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:1767DE69-A68F-44AD-A821-924352B87079@.microsoft.com...
> > What SQL Server permission(s) are required to both modify and execute jobs
> > in
> > SQL Server 2000 and SQL Server 2005?
> >
> > Thank you.
> > --
> > Mike
>
>|||BOL for 2005 has lots of very good information on this subject. Much more
than you can expect someone to post here. There is no reason to reinvent the
wheel so have a look at this section of BOL for a start.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d728a2f5-6a2d-40f7-aba7-ed51b930072d.htm
Andrew J. Kelly SQL MVP
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:63F20246-5FAC-421F-9A2E-80C68064226F@.microsoft.com...
>I don 't see an answer for SQL Server 2005 (please see below). Any help
> would be appreciated. Thank you.
> SQL Server 2005
> 1) I haven't played to much with this issue. If I remember well you can
> create a proxy account to run jobs
> with an acount that is not a member of sysadmin server role.
> --
> Mike
>
> "John Bell" wrote:
>> Mike
>> You posted this in microsoft.public.sqlserver.security on 6 Jan, and
>> microsoft.public.sqlserver.dts 8 Jan Uri and Allan have replied to this.
>> Please do not muti-post http://www.aspfaq.com/etiquette.asp?id=5003
>> John
>> "Mike" <Mike@.discussions.microsoft.com> wrote in message
>> news:1767DE69-A68F-44AD-A821-924352B87079@.microsoft.com...
>> > What SQL Server permission(s) are required to both modify and execute
>> > jobs
>> > in
>> > SQL Server 2000 and SQL Server 2005?
>> >
>> > Thank you.
>> > --
>> > Mike
>>|||Are you a Microsoft employee? If not, I still don't have answers for the
question below. Microsoft, if you could please help, I would highly
appreciate it.
Are different permissions required to both modify and run SQL Server 2000
jobs and DTS packages?
Thank you.
--
Mike
"Andrew J. Kelly" wrote:
> BOL for 2005 has lots of very good information on this subject. Much more
> than you can expect someone to post here. There is no reason to reinvent the
> wheel so have a look at this section of BOL for a start.
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d728a2f5-6a2d-40f7-aba7-ed51b930072d.htm
>
> --
> Andrew J. Kelly SQL MVP
>
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:63F20246-5FAC-421F-9A2E-80C68064226F@.microsoft.com...
> >I don 't see an answer for SQL Server 2005 (please see below). Any help
> > would be appreciated. Thank you.
> >
> > SQL Server 2005
> >
> > 1) I haven't played to much with this issue. If I remember well you can
> > create a proxy account to run jobs
> > with an acount that is not a member of sysadmin server role.
> > --
> > Mike
> >
> >
> > "John Bell" wrote:
> >
> >> Mike
> >>
> >> You posted this in microsoft.public.sqlserver.security on 6 Jan, and
> >> microsoft.public.sqlserver.dts 8 Jan Uri and Allan have replied to this.
> >> Please do not muti-post http://www.aspfaq.com/etiquette.asp?id=5003
> >>
> >> John
> >>
> >> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> >> news:1767DE69-A68F-44AD-A821-924352B87079@.microsoft.com...
> >> > What SQL Server permission(s) are required to both modify and execute
> >> > jobs
> >> > in
> >> > SQL Server 2000 and SQL Server 2005?
> >> >
> >> > Thank you.
> >> > --
> >> > Mike
> >>
> >>
> >>
>
>|||What does my employment status have to do with if you have an answer or not?
You specifically stated that you needed an answer for SQL2005. This is from
your last post:
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:63F20246-5FAC-421F-9A2E-80C68064226F@.microsoft.com...
> >I don 't see an answer for SQL Server 2005 (please see below). Any help
> > would be appreciated. Thank you.
> >
And here is the answer that Allan Mitchell gave you in the DTS group that
you cross posted to.
Hello mike,
In 2K, So long as you can access the package you can change it.
In 2K5 a way of implementing security can be found here in BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/9702e90c-fada-4978-a473-1b1423017d80.htm
<<
You replied:
Thank you, and have a great weekend.
--
Mike
<<
So I am confused as to what you really want. Do you just not trust the
answers since we are not MS employees or is there something else?
Andrew J. Kelly SQL MVP
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:E42F971F-E556-4D5B-9203-2AF587D4A7A6@.microsoft.com...
> Are you a Microsoft employee? If not, I still don't have answers for the
> question below. Microsoft, if you could please help, I would highly
> appreciate it.
> Are different permissions required to both modify and run SQL Server 2000
> jobs and DTS packages?
> Thank you.
> --
> Mike
>
> "Andrew J. Kelly" wrote:
>> BOL for 2005 has lots of very good information on this subject. Much
>> more
>> than you can expect someone to post here. There is no reason to reinvent
>> the
>> wheel so have a look at this section of BOL for a start.
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d728a2f5-6a2d-40f7-aba7-ed51b930072d.htm
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Mike" <Mike@.discussions.microsoft.com> wrote in message
>> news:63F20246-5FAC-421F-9A2E-80C68064226F@.microsoft.com...
>> >I don 't see an answer for SQL Server 2005 (please see below). Any help
>> > would be appreciated. Thank you.
>> >
>> > SQL Server 2005
>> >
>> > 1) I haven't played to much with this issue. If I remember well you can
>> > create a proxy account to run jobs
>> > with an acount that is not a member of sysadmin server role.
>> > --
>> > Mike
>> >
>> >
>> > "John Bell" wrote:
>> >
>> >> Mike
>> >>
>> >> You posted this in microsoft.public.sqlserver.security on 6 Jan, and
>> >> microsoft.public.sqlserver.dts 8 Jan Uri and Allan have replied to
>> >> this.
>> >> Please do not muti-post http://www.aspfaq.com/etiquette.asp?id=5003
>> >>
>> >> John
>> >>
>> >> "Mike" <Mike@.discussions.microsoft.com> wrote in message
>> >> news:1767DE69-A68F-44AD-A821-924352B87079@.microsoft.com...
>> >> > What SQL Server permission(s) are required to both modify and
>> >> > execute
>> >> > jobs
>> >> > in
>> >> > SQL Server 2000 and SQL Server 2005?
>> >> >
>> >> > Thank you.
>> >> > --
>> >> > Mike
>> >>
>> >>
>> >>
>>|||The BOL link that Andy posted has 100% of the information you are looking
for in it. Microsoft spends millions on writing BOL, so that answers to at
least 80% or more of the questions that could get asked are already
answered, as long as people actually read it.
Someone's employment status has absolutely nothing whatsoever to do with
posting an answer. No, Andy isn't a Microsoft employee and that is
completely irrelevant. He is a SQL Server MVP and has been for a long time.
The MVP program was created to provide online support for customers of
products. With a few exceptions, the people in the MVP program are the best
information resources that exist in the industry.
If you want anyone to actually answer your questions, then I'd suggest that
you don't insult people who take their time to answer questions.
--
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:E42F971F-E556-4D5B-9203-2AF587D4A7A6@.microsoft.com...
> Are you a Microsoft employee? If not, I still don't have answers for the
> question below. Microsoft, if you could please help, I would highly
> appreciate it.
> Are different permissions required to both modify and run SQL Server 2000
> jobs and DTS packages?
> Thank you.
> --
> Mike
>
> "Andrew J. Kelly" wrote:
>> BOL for 2005 has lots of very good information on this subject. Much
>> more
>> than you can expect someone to post here. There is no reason to reinvent
>> the
>> wheel so have a look at this section of BOL for a start.
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d728a2f5-6a2d-40f7-aba7-ed51b930072d.htm
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Mike" <Mike@.discussions.microsoft.com> wrote in message
>> news:63F20246-5FAC-421F-9A2E-80C68064226F@.microsoft.com...
>> >I don 't see an answer for SQL Server 2005 (please see below). Any help
>> > would be appreciated. Thank you.
>> >
>> > SQL Server 2005
>> >
>> > 1) I haven't played to much with this issue. If I remember well you can
>> > create a proxy account to run jobs
>> > with an acount that is not a member of sysadmin server role.
>> > --
>> > Mike
>> >
>> >
>> > "John Bell" wrote:
>> >
>> >> Mike
>> >>
>> >> You posted this in microsoft.public.sqlserver.security on 6 Jan, and
>> >> microsoft.public.sqlserver.dts 8 Jan Uri and Allan have replied to
>> >> this.
>> >> Please do not muti-post http://www.aspfaq.com/etiquette.asp?id=5003
>> >>
>> >> John
>> >>
>> >> "Mike" <Mike@.discussions.microsoft.com> wrote in message
>> >> news:1767DE69-A68F-44AD-A821-924352B87079@.microsoft.com...
>> >> > What SQL Server permission(s) are required to both modify and
>> >> > execute
>> >> > jobs
>> >> > in
>> >> > SQL Server 2000 and SQL Server 2005?
>> >> >
>> >> > Thank you.
>> >> > --
>> >> > Mike
>> >>
>> >>
>> >>
>>

Permission/role needed to both modify and execute jobs

What SQL Server permission(s) are required to both modify and execute jobs in
SQL Server 2000 and SQL Server 2005?
Thank you.
Mike
Mike
You posted this in microsoft.public.sqlserver.security on 6 Jan, and
microsoft.public.sqlserver.dts 8 Jan Uri and Allan have replied to this.
Please do not muti-post http://www.aspfaq.com/etiquette.asp?id=5003
John
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:1767DE69-A68F-44AD-A821-924352B87079@.microsoft.com...
> What SQL Server permission(s) are required to both modify and execute jobs
> in
> SQL Server 2000 and SQL Server 2005?
> Thank you.
> --
> Mike
|||I don 't see an answer for SQL Server 2005 (please see below). Any help
would be appreciated. Thank you.
SQL Server 2005
1) I haven't played to much with this issue. If I remember well you can
create a proxy account to run jobs
with an acount that is not a member of sysadmin server role.
Mike
"John Bell" wrote:

> Mike
> You posted this in microsoft.public.sqlserver.security on 6 Jan, and
> microsoft.public.sqlserver.dts 8 Jan Uri and Allan have replied to this.
> Please do not muti-post http://www.aspfaq.com/etiquette.asp?id=5003
> John
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:1767DE69-A68F-44AD-A821-924352B87079@.microsoft.com...
>
>
|||Additionally, if you would have read my new questions, you would have noticed
that they had changed from just run, to both modify and run/execute.
Thank you.
Mike
"John Bell" wrote:

> Mike
> You posted this in microsoft.public.sqlserver.security on 6 Jan, and
> microsoft.public.sqlserver.dts 8 Jan Uri and Allan have replied to this.
> Please do not muti-post http://www.aspfaq.com/etiquette.asp?id=5003
> John
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:1767DE69-A68F-44AD-A821-924352B87079@.microsoft.com...
>
>
|||BOL for 2005 has lots of very good information on this subject. Much more
than you can expect someone to post here. There is no reason to reinvent the
wheel so have a look at this section of BOL for a start.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d728a2f5-6a2d-40f7-aba7-ed51b930072d.htm
Andrew J. Kelly SQL MVP
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:63F20246-5FAC-421F-9A2E-80C68064226F@.microsoft.com...[vbcol=seagreen]
>I don 't see an answer for SQL Server 2005 (please see below). Any help
> would be appreciated. Thank you.
> SQL Server 2005
> 1) I haven't played to much with this issue. If I remember well you can
> create a proxy account to run jobs
> with an acount that is not a member of sysadmin server role.
> --
> Mike
>
> "John Bell" wrote:
|||Are you a Microsoft employee? If not, I still don't have answers for the
question below. Microsoft, if you could please help, I would highly
appreciate it.
Are different permissions required to both modify and run SQL Server 2000
jobs and DTS packages?
Thank you.
Mike
"Andrew J. Kelly" wrote:

> BOL for 2005 has lots of very good information on this subject. Much more
> than you can expect someone to post here. There is no reason to reinvent the
> wheel so have a look at this section of BOL for a start.
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d728a2f5-6a2d-40f7-aba7-ed51b930072d.htm
>
> --
> Andrew J. Kelly SQL MVP
>
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:63F20246-5FAC-421F-9A2E-80C68064226F@.microsoft.com...
>
>
|||What does my employment status have to do with if you have an answer or not?
You specifically stated that you needed an answer for SQL2005. This is from
your last post:
[vbcol=seagreen]
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:63F20246-5FAC-421F-9A2E-80C68064226F@.microsoft.com...
And here is the answer that Allan Mitchell gave you in the DTS group that
you cross posted to.
[vbcol=seagreen]
Hello mike,
In 2K, So long as you can access the package you can change it.
In 2K5 a way of implementing security can be found here in BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/9702e90c-fada-4978-a473-1b1423017d80.htm
<<
You replied:
[vbcol=seagreen]
Thank you, and have a great weekend.
Mike
<<
So I am confused as to what you really want. Do you just not trust the
answers since we are not MS employees or is there something else?
Andrew J. Kelly SQL MVP
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:E42F971F-E556-4D5B-9203-2AF587D4A7A6@.microsoft.com...[vbcol=seagreen]
> Are you a Microsoft employee? If not, I still don't have answers for the
> question below. Microsoft, if you could please help, I would highly
> appreciate it.
> Are different permissions required to both modify and run SQL Server 2000
> jobs and DTS packages?
> Thank you.
> --
> Mike
>
> "Andrew J. Kelly" wrote:
|||The BOL link that Andy posted has 100% of the information you are looking
for in it. Microsoft spends millions on writing BOL, so that answers to at
least 80% or more of the questions that could get asked are already
answered, as long as people actually read it.
Someone's employment status has absolutely nothing whatsoever to do with
posting an answer. No, Andy isn't a Microsoft employee and that is
completely irrelevant. He is a SQL Server MVP and has been for a long time.
The MVP program was created to provide online support for customers of
products. With a few exceptions, the people in the MVP program are the best
information resources that exist in the industry.
If you want anyone to actually answer your questions, then I'd suggest that
you don't insult people who take their time to answer questions.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:E42F971F-E556-4D5B-9203-2AF587D4A7A6@.microsoft.com...[vbcol=seagreen]
> Are you a Microsoft employee? If not, I still don't have answers for the
> question below. Microsoft, if you could please help, I would highly
> appreciate it.
> Are different permissions required to both modify and run SQL Server 2000
> jobs and DTS packages?
> Thank you.
> --
> Mike
>
> "Andrew J. Kelly" wrote:

Permission/role needed to both modify and execute jobs

What SQL Server permission(s) are required to both modify and execute jobs i
n
SQL Server 2000 and SQL Server 2005?
Thank you.
--
MikeMike
You posted this in microsoft.public.sqlserver.security on 6 Jan, and
microsoft.public.sqlserver.dts 8 Jan Uri and Allan have replied to this.
Please do not muti-post http://www.aspfaq.com/etiquette.asp?id=5003
John
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:1767DE69-A68F-44AD-A821-924352B87079@.microsoft.com...
> What SQL Server permission(s) are required to both modify and execute jobs
> in
> SQL Server 2000 and SQL Server 2005?
> Thank you.
> --
> Mike|||I don 't see an answer for SQL Server 2005 (please see below). Any help
would be appreciated. Thank you.
SQL Server 2005
1) I haven't played to much with this issue. If I remember well you can
create a proxy account to run jobs
with an acount that is not a member of sysadmin server role.
--
Mike
"John Bell" wrote:

> Mike
> You posted this in microsoft.public.sqlserver.security on 6 Jan, and
> microsoft.public.sqlserver.dts 8 Jan Uri and Allan have replied to this.
> Please do not muti-post http://www.aspfaq.com/etiquette.asp?id=5003
> John
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:1767DE69-A68F-44AD-A821-924352B87079@.microsoft.com...
>
>|||Additionally, if you would have read my new questions, you would have notice
d
that they had changed from just run, to both modify and run/execute.
Thank you.
--
Mike
"John Bell" wrote:

> Mike
> You posted this in microsoft.public.sqlserver.security on 6 Jan, and
> microsoft.public.sqlserver.dts 8 Jan Uri and Allan have replied to this.
> Please do not muti-post http://www.aspfaq.com/etiquette.asp?id=5003
> John
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:1767DE69-A68F-44AD-A821-924352B87079@.microsoft.com...
>
>|||BOL for 2005 has lots of very good information on this subject. Much more
than you can expect someone to post here. There is no reason to reinvent the
wheel so have a look at this section of BOL for a start.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d728a2f5-6a2d-40f7-aba7-ed51
b930072d.htm
Andrew J. Kelly SQL MVP
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:63F20246-5FAC-421F-9A2E-80C68064226F@.microsoft.com...[vbcol=seagreen]
>I don 't see an answer for SQL Server 2005 (please see below). Any help
> would be appreciated. Thank you.
> SQL Server 2005
> 1) I haven't played to much with this issue. If I remember well you can
> create a proxy account to run jobs
> with an acount that is not a member of sysadmin server role.
> --
> Mike
>
> "John Bell" wrote:
>|||Are you a Microsoft employee? If not, I still don't have answers for the
question below. Microsoft, if you could please help, I would highly
appreciate it.
Are different permissions required to both modify and run SQL Server 2000
jobs and DTS packages?
Thank you.
--
Mike
"Andrew J. Kelly" wrote:

> BOL for 2005 has lots of very good information on this subject. Much more
> than you can expect someone to post here. There is no reason to reinvent t
he
> wheel so have a look at this section of BOL for a start.
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d728a2f5-6a2d-40f7-aba7-ed
51b930072d.htm
>
> --
> Andrew J. Kelly SQL MVP
>
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:63F20246-5FAC-421F-9A2E-80C68064226F@.microsoft.com...
>
>|||What does my employment status have to do with if you have an answer or not?
You specifically stated that you needed an answer for SQL2005. This is from
your last post:
[vbcol=seagreen]
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:63F20246-5FAC-421F-9A2E-80C68064226F@.microsoft.com...
And here is the answer that Allan Mitchell gave you in the DTS group that
you cross posted to.
[vbcol=seagreen]
Hello mike,
In 2K, So long as you can access the package you can change it.
In 2K5 a way of implementing security can be found here in BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/9702e90c-fada-4978-a473-1
b1423017d80.htm
<<
You replied:
[vbcol=seagreen]
Thank you, and have a great weekend.
--
Mike
<<
So I am confused as to what you really want. Do you just not trust the
answers since we are not MS employees or is there something else?
Andrew J. Kelly SQL MVP
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:E42F971F-E556-4D5B-9203-2AF587D4A7A6@.microsoft.com...[vbcol=seagreen]
> Are you a Microsoft employee? If not, I still don't have answers for the
> question below. Microsoft, if you could please help, I would highly
> appreciate it.
> Are different permissions required to both modify and run SQL Server 2000
> jobs and DTS packages?
> Thank you.
> --
> Mike
>
> "Andrew J. Kelly" wrote:
>|||The BOL link that Andy posted has 100% of the information you are looking
for in it. Microsoft spends millions on writing BOL, so that answers to at
least 80% or more of the questions that could get asked are already
answered, as long as people actually read it.
Someone's employment status has absolutely nothing whatsoever to do with
posting an answer. No, Andy isn't a Microsoft employee and that is
completely irrelevant. He is a SQL Server MVP and has been for a long time.
The MVP program was created to provide online support for customers of
products. With a few exceptions, the people in the MVP program are the best
information resources that exist in the industry.
If you want anyone to actually answer your questions, then I'd suggest that
you don't insult people who take their time to answer questions.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:E42F971F-E556-4D5B-9203-2AF587D4A7A6@.microsoft.com...[vbcol=seagreen]
> Are you a Microsoft employee? If not, I still don't have answers for the
> question below. Microsoft, if you could please help, I would highly
> appreciate it.
> Are different permissions required to both modify and run SQL Server 2000
> jobs and DTS packages?
> Thank you.
> --
> Mike
>
> "Andrew J. Kelly" wrote:
>

Permission to execute a stored procedure

What is the minimum permission required to execute a stored procedure
in DBAdd the login as a user in the database and then use
GRANT EXECUTE ON procname TO username
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL novice" <balacr@.gmail.com> wrote in message
news:1129793471.910029.122070@.g43g2000cwa.googlegroups.com...
> What is the minimum permission required to execute a stored procedure
> in DB
>|||Execute rights.
GRANT EXECUTE ON <objectname> to <Username>
Refer to the BOL.
HTH, Suessmeyer.|||Thanks for your answers.
But can I give permission to all the stored procedures some other way?|||You will have to write a small script that does it for you.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"SQL novice" <balacr@.gmail.com> wrote in message
news:1129798105.010965.108580@.z14g2000cwz.googlegroups.com...
> Thanks for your answers.
> But can I give permission to all the stored procedures some other way?
>|||And the script is:
============
You can loop through a cursor and execute the commands:
http://www.codeproject.com/database/T-SQL.asp
or you execute this scripts and execute the output in QA:
DECLARE @.GroupORRole VARCHAR(500)
SET @.GroupORRole = 'Bob'
SELECT 'GRANT EXECUTE ON ' + ROUTINE_NAME + ' TO ' + @.GroupORRole +
CHAR(10) + CHAR(13) + 'GO' + CHAR(10) + CHAR(13)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' --for all Procedures
HTH; Jens Suessmeyer.|||Try
SELECT 'GRANT EXECUTE ON '+ROUTINE_NAME+' TO John'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
'IsMsShipped')=0
"SQL novice" <balacr@.gmail.com> wrote in message
news:1129798105.010965.108580@.z14g2000cwz.googlegroups.com...
> Thanks for your answers.
> But can I give permission to all the stored procedures some other way?
>

Permission required to run the Profiler and the Debugger

Does anyone know what permission to grant (other than SA) in order to enable
the usage of SQL Profiler 2005 and the debugger on a 2005 installation?
thanks
Cordelia
Give the permission ALTER TRACE to execute profiler.
Debugging can be done trhu only the visual studio.
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124550
Thanks
Hari
"Cordelia" <Cordelia@.discussions.microsoft.com> wrote in message
news:57E838C3-F49D-4283-BF8F-D37701E195CA@.microsoft.com...
> Does anyone know what permission to grant (other than SA) in order to
> enable
> the usage of SQL Profiler 2005 and the debugger on a 2005 installation?
> thanks
> Cordelia
|||Thanks Hari for the info. Granting ALTER TRACE seems to have done the trick
for runing the Profiler. According to the BOL, ALTER TRACE enable the
account to replay a trace file. Any idea what else does ALTER TRACE provide
permission for?
As for the debugger, does an account require any permission setup to be able
to debug via Visual Studio?
Cordelia
"Hari Prasad" wrote:

>
> Give the permission ALTER TRACE to execute profiler.
> Debugging can be done trhu only the visual studio.
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124550
>
> Thanks
> Hari
> "Cordelia" <Cordelia@.discussions.microsoft.com> wrote in message
> news:57E838C3-F49D-4283-BF8F-D37701E195CA@.microsoft.com...
>
>

permission required for a backup operator

I have installed SQL Server 2000 in a Wondows 2000 Server, and I need to set
permissions for a user to only backup data bases to tape media, I have tried
everything without any results, does any one have any clue about this.Have you tried granting BACKUP DATABASE statement permissions? Another
method is to add the user to the db_backupoperator fixed database role.
Hope this helps.
Dan Guzman
SQL Server MVP
"Carlos Caballero" <CarlosCaballero@.discussions.microsoft.com> wrote in
message news:24025B3A-A2AF-4AA3-84AE-0893E76D4A26@.microsoft.com...
>I have installed SQL Server 2000 in a Wondows 2000 Server, and I need to
>set
> permissions for a user to only backup data bases to tape media, I have
> tried
> everything without any results, does any one have any clue about this.|||Dan,
I have fixed the user as an db_backupoperator database role and I still have
the same problem, the thing is that when the operator try to backup the
database he can't use the tape device. I have tried creating a backup device
pointing to the tape but the operator still can't see it. Do you have any
other idea?
"Dan Guzman" wrote:

> Have you tried granting BACKUP DATABASE statement permissions? Another
> method is to add the user to the db_backupoperator fixed database role.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Carlos Caballero" <CarlosCaballero@.discussions.microsoft.com> wrote in
> message news:24025B3A-A2AF-4AA3-84AE-0893E76D4A26@.microsoft.com...
>
>|||What is the exact error message? Any related messages in the SQL Server
error log? Can the user backup to disk?
Hope this helps.
Dan Guzman
SQL Server MVP
"Carlos Caballero" <CarlosCaballero@.discussions.microsoft.com> wrote in
message news:4575CACD-B488-4468-9E76-D0429E8BF231@.microsoft.com...[vbcol=seagreen]
> Dan,
> I have fixed the user as an db_backupoperator database role and I still
> have
> the same problem, the thing is that when the operator try to backup the
> database he can't use the tape device. I have tried creating a backup
> device
> pointing to the tape but the operator still can't see it. Do you have any
> other idea?
> "Dan Guzman" wrote:
>

permission grant set is incompatible between appdomains

HI ,
I have a custom dll that I use to do some of the code expressions across
reports. I have set the required permissions in the rsrvpolicy.config file to
allow it to run under full trust. I have also set the following attributes
on the method in that dll
<System.Security.Permissions.EnvironmentPermission(Security.Permissions.SecurityAction.Assert, Unrestricted:=True),
System.Net.WebPermission(Security.Permissions.SecurityAction.Assert,
Unrestricted:=True)>
Everything was fine till yesterday. Yesterday, my machine was added to be a
member of a domain. I am not getting this error when I try to access the
reports:
rsProcessingError 400 Failed to load expression host assembly. Details:
Assembly mycustom.dll security permission grant set is incompatible between
appdomains.
http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings.resources.Strings&EvtID=rsProcessingError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=8.00
Microsoft SQL Server Reporting Services 8.00.743.00 127 OsIndependent 1033
Microsoft.ReportingServices.Processing Failed to load expression host
assembly. Details: Assembly mycustom.dll security permission grant set is
incompatible between appdomains
Please help!Never mind.
I got some notes by searching in google that fixed this problem. I just had
to restart IIS.
and it starting working
Thanks
George