 | |
Re: Backup using the proxy account in SQL2005
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: Backup using the proxy account in SQL2005 -
12-20-2006
, 09:59 AM
|
So the backup is produced but you get package failed. I suggest you post this to an SSIS group. Next step would be to investigate why the SSIS package fails, looking at SSIS log etc, but since I'm no SSIS person, I don't have much to offer, I'm afraid... -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:ODu23y7IHHA.420 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Tibor, I first granted Backup Database and then Backup Log to the proxy account and added it to the application database in the db_backupoperator role. It appears to perform the backup but still says package failed. Any ideas? Thanks Chris "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:u6XuY86IHHA.3952 (AT) TK2MSFTNGP02 (DOT) phx.gbl... The login in question need to have a user in the database you want to backup and in that database be db_backupoperator or have granted the BACKUP DATABASE and/or BACKUP LOG privileges. I don't think that there's a privilege you can grant to a login that allow you to backup any database, less than CONTROL (same as sysadmin). At least, that is how I read below: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/499e5ed6-945c-4791-ab45-68dec0b9c289.htm -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:%23uMeVy6IHHA.5104 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Hi, We want to use the proxy account to take backups created from maintenance plans in SQL2005. What permissions does the proxy account need to do this? It has permissions to run SSIS packages and it failed saying the package could not be loaded. I then added the proxy account to the db_dtsadmin role and now I get the package execution failed. Does it also need the master role db_backupoperator to run? Thanks Chris |
#2
| |||
| |||
|
|
Thanks again Tibor. I had looked in SSIS at the package and it had mentioned the Reader Role as db_dtsadmin, db_dtsoperator and the creator of the package. I can see as long as I give the proxy account one of the 2 db_dts roles in msdb it can read/execute the package. I suppose I could change this to some other msdb role and make sure the proxy account is a member of that role. I cannot see much about the error. It throws an Event 12291 in the Event log saying the package has failed. This is running SQL2005 SP1. Thanks Chris "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:eQXzvVBJHHA.960 (AT) TK2MSFTNGP04 (DOT) phx.gbl... So the backup is produced but you get package failed. I suggest you post this to an SSIS group. Next step would be to investigate why the SSIS package fails, looking at SSIS log etc, but since I'm no SSIS person, I don't have much to offer, I'm afraid... -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:ODu23y7IHHA.420 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Tibor, I first granted Backup Database and then Backup Log to the proxy account and added it to the application database in the db_backupoperator role. It appears to perform the backup but still says package failed. Any ideas? Thanks Chris "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:u6XuY86IHHA.3952 (AT) TK2MSFTNGP02 (DOT) phx.gbl... The login in question need to have a user in the database you want to backup and in that database be db_backupoperator or have granted the BACKUP DATABASE and/or BACKUP LOG privileges. I don't think that there's a privilege you can grant to a login that allow you to backup any database, less than CONTROL (same as sysadmin). At least, that is how I read below: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/499e5ed6-945c-4791-ab45-68dec0b9c289.htm -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:%23uMeVy6IHHA.5104 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Hi, We want to use the proxy account to take backups created from maintenance plans in SQL2005. What permissions does the proxy account need to do this? It has permissions to run SSIS packages and it failed saying the package could not be loaded. I then added the proxy account to the db_dtsadmin role and now I get the package execution failed. Does it also need the master role db_backupoperator to run? Thanks Chris |
#3
| |||
| |||
|
|
Have I found a bug in DEC SP2? |
the steps needed), you probably want to post this to
http://connect.microsoft.com/site/si...aspx?SiteID=68
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote
Quote:
|
The new SP2 Dec CTP does give a lot more info when I try to run this. It complains about sp_maintplan_logentry. It mentions the Execute permission on this msdb stored proc was denied. Have I found a bug in DEC SP2? Thanks Chris "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:uVPZH$EJHHA.1816 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Thanks again Tibor. I had looked in SSIS at the package and it had mentioned the Reader Role as db_dtsadmin, db_dtsoperator and the creator of the package. I can see as long as I give the proxy account one of the 2 db_dts roles in msdb it can read/execute the package. I suppose I could change this to some other msdb role and make sure the proxy account is a member of that role. I cannot see much about the error. It throws an Event 12291 in the Event log saying the package has failed. This is running SQL2005 SP1. Thanks Chris "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:eQXzvVBJHHA.960 (AT) TK2MSFTNGP04 (DOT) phx.gbl... So the backup is produced but you get package failed. I suggest you post this to an SSIS group. Next step would be to investigate why the SSIS package fails, looking at SSIS log etc, but since I'm no SSIS person, I don't have much to offer, I'm afraid... -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:ODu23y7IHHA.420 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Tibor, I first granted Backup Database and then Backup Log to the proxy account and added it to the application database in the db_backupoperator role. It appears to perform the backup but still says package failed. Any ideas? Thanks Chris "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:u6XuY86IHHA.3952 (AT) TK2MSFTNGP02 (DOT) phx.gbl... The login in question need to have a user in the database you want to backup and in that database be db_backupoperator or have granted the BACKUP DATABASE and/or BACKUP LOG privileges. I don't think that there's a privilege you can grant to a login that allow you to backup any database, less than CONTROL (same as sysadmin). At least, that is how I read below: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/499e5ed6-945c-4791-ab45-68dec0b9c289.htm -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:%23uMeVy6IHHA.5104 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Hi, We want to use the proxy account to take backups created from maintenance plans in SQL2005. What permissions does the proxy account need to do this? It has permissions to run SSIS packages and it failed saying the package could not be loaded. I then added the proxy account to the db_dtsadmin role and now I get the package execution failed. Does it also need the master role db_backupoperator to run? Thanks Chris |
#4
| |||
| |||
|
|
Have I found a bug in DEC SP2? Possibly. I know MS are working hard on Maint Plans in SP2, so if you can create a repro (specify the steps needed), you probably want to post this to http://connect.microsoft.com/site/si...aspx?SiteID=68 -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:OiFy8tIJHHA.3952 (AT) TK2MSFTNGP02 (DOT) phx.gbl... The new SP2 Dec CTP does give a lot more info when I try to run this. It complains about sp_maintplan_logentry. It mentions the Execute permission on this msdb stored proc was denied. Have I found a bug in DEC SP2? Thanks Chris "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:uVPZH$EJHHA.1816 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Thanks again Tibor. I had looked in SSIS at the package and it had mentioned the Reader Role as db_dtsadmin, db_dtsoperator and the creator of the package. I can see as long as I give the proxy account one of the 2 db_dts roles in msdb it can read/execute the package. I suppose I could change this to some other msdb role and make sure the proxy account is a member of that role. I cannot see much about the error. It throws an Event 12291 in the Event log saying the package has failed. This is running SQL2005 SP1. Thanks Chris "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:eQXzvVBJHHA.960 (AT) TK2MSFTNGP04 (DOT) phx.gbl... So the backup is produced but you get package failed. I suggest you post this to an SSIS group. Next step would be to investigate why the SSIS package fails, looking at SSIS log etc, but since I'm no SSIS person, I don't have much to offer, I'm afraid... -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:ODu23y7IHHA.420 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Tibor, I first granted Backup Database and then Backup Log to the proxy account and added it to the application database in the db_backupoperator role. It appears to perform the backup but still says package failed. Any ideas? Thanks Chris "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com wrote The login in question need to have a user in the database you want to backup and in that database be db_backupoperator or have granted the BACKUP DATABASE and/or BACKUP LOG privileges. I don't think that there's a privilege you can grant to a login that allow you to backup any database, less than CONTROL (same as sysadmin). At least, that is how I read below: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/499e5ed6-945c-4791-ab45-68dec0b9c289.htm -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:%23uMeVy6IHHA.5104 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Hi, We want to use the proxy account to take backups created from maintenance plans in SQL2005. What permissions does the proxy account need to do this? It has permissions to run SSIS packages and it failed saying the package could not be loaded. I then added the proxy account to the db_dtsadmin role and now I get the package execution failed. Does it also need the master role db_backupoperator to run? Thanks Chris |
#5
| |||
| |||
|
|
I also had another error with trying to access SSIS when SQL2005 is not the default instance. |
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote
Quote:
|
That's just what I was thinking of doing. I also had another error with trying to access SSIS when SQL2005 is not the default instance. Thanks Chris "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:%23N718tNJHHA.3872 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Have I found a bug in DEC SP2? Possibly. I know MS are working hard on Maint Plans in SP2, so if you can create a repro (specify the steps needed), you probably want to post this to http://connect.microsoft.com/site/si...aspx?SiteID=68 -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:OiFy8tIJHHA.3952 (AT) TK2MSFTNGP02 (DOT) phx.gbl... The new SP2 Dec CTP does give a lot more info when I try to run this. It complains about sp_maintplan_logentry. It mentions the Execute permission on this msdb stored proc was denied. Have I found a bug in DEC SP2? Thanks Chris "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:uVPZH$EJHHA.1816 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Thanks again Tibor. I had looked in SSIS at the package and it had mentioned the Reader Role as db_dtsadmin, db_dtsoperator and the creator of the package. I can see as long as I give the proxy account one of the 2 db_dts roles in msdb it can read/execute the package. I suppose I could change this to some other msdb role and make sure the proxy account is a member of that role. I cannot see much about the error. It throws an Event 12291 in the Event log saying the package has failed. This is running SQL2005 SP1. Thanks Chris "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:eQXzvVBJHHA.960 (AT) TK2MSFTNGP04 (DOT) phx.gbl... So the backup is produced but you get package failed. I suggest you post this to an SSIS group. Next step would be to investigate why the SSIS package fails, looking at SSIS log etc, but since I'm no SSIS person, I don't have much to offer, I'm afraid... -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:ODu23y7IHHA.420 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Tibor, I first granted Backup Database and then Backup Log to the proxy account and added it to the application database in the db_backupoperator role. It appears to perform the backup but still says package failed. Any ideas? Thanks Chris "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:u6XuY86IHHA.3952 (AT) TK2MSFTNGP02 (DOT) phx.gbl... The login in question need to have a user in the database you want to backup and in that database be db_backupoperator or have granted the BACKUP DATABASE and/or BACKUP LOG privileges. I don't think that there's a privilege you can grant to a login that allow you to backup any database, less than CONTROL (same as sysadmin). At least, that is how I read below: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/499e5ed6-945c-4791-ab45-68dec0b9c289.htm -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:%23uMeVy6IHHA.5104 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Hi, We want to use the proxy account to take backups created from maintenance plans in SQL2005. What permissions does the proxy account need to do this? It has permissions to run SSIS packages and it failed saying the package could not be loaded. I then added the proxy account to the db_dtsadmin role and now I get the package execution failed. Does it also need the master role db_backupoperator to run? Thanks Chris |
#6
| |||
| |||
|
|
I also had another error with trying to access SSIS when SQL2005 is not the default instance. Make sure you specify the correct instance name in the MsDtsSrvr.ini.xml file. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:uttfWBRJHHA.1248 (AT) TK2MSFTNGP02 (DOT) phx.gbl... That's just what I was thinking of doing. I also had another error with trying to access SSIS when SQL2005 is not the default instance. Thanks Chris "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:%23N718tNJHHA.3872 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Have I found a bug in DEC SP2? Possibly. I know MS are working hard on Maint Plans in SP2, so if you can create a repro (specify the steps needed), you probably want to post this to http://connect.microsoft.com/site/si...aspx?SiteID=68 -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:OiFy8tIJHHA.3952 (AT) TK2MSFTNGP02 (DOT) phx.gbl... The new SP2 Dec CTP does give a lot more info when I try to run this. It complains about sp_maintplan_logentry. It mentions the Execute permission on this msdb stored proc was denied. Have I found a bug in DEC SP2? Thanks Chris "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:uVPZH$EJHHA.1816 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Thanks again Tibor. I had looked in SSIS at the package and it had mentioned the Reader Role as db_dtsadmin, db_dtsoperator and the creator of the package. I can see as long as I give the proxy account one of the 2 db_dts roles in msdb it can read/execute the package. I suppose I could change this to some other msdb role and make sure the proxy account is a member of that role. I cannot see much about the error. It throws an Event 12291 in the Event log saying the package has failed. This is running SQL2005 SP1. Thanks Chris "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com wrote So the backup is produced but you get package failed. I suggest you post this to an SSIS group. Next step would be to investigate why the SSIS package fails, looking at SSIS log etc, but since I'm no SSIS person, I don't have much to offer, I'm afraid... -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:ODu23y7IHHA.420 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Tibor, I first granted Backup Database and then Backup Log to the proxy account and added it to the application database in the db_backupoperator role. It appears to perform the backup but still says package failed. Any ideas? Thanks Chris "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com wrote The login in question need to have a user in the database you want to backup and in that database be db_backupoperator or have granted the BACKUP DATABASE and/or BACKUP LOG privileges. I don't think that there's a privilege you can grant to a login that allow you to backup any database, less than CONTROL (same as sysadmin). At least, that is how I read below: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/499e5ed6-945c-4791-ab45-68dec0b9c289.htm -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:%23uMeVy6IHHA.5104 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Hi, We want to use the proxy account to take backups created from maintenance plans in SQL2005. What permissions does the proxy account need to do this? It has permissions to run SSIS packages and it failed saying the package could not be loaded. I then added the proxy account to the db_dtsadmin role and now I get the package execution failed. Does it also need the master role db_backupoperator to run? Thanks Chris |
#7
| |||
| |||
|
|
Tibor, It does not have any instance name in this xml file. Where should it go? My environment is SQL2000 SP4 is the default. I have 3 named instances, SQL2005 RTM, SQL2005SP1 and now SQL2005SP2. Chris "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:uUkskTSJHHA.4760 (AT) TK2MSFTNGP03 (DOT) phx.gbl... I also had another error with trying to access SSIS when SQL2005 is not the default instance. Make sure you specify the correct instance name in the MsDtsSrvr.ini.xml file. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:uttfWBRJHHA.1248 (AT) TK2MSFTNGP02 (DOT) phx.gbl... That's just what I was thinking of doing. I also had another error with trying to access SSIS when SQL2005 is not the default instance. Thanks Chris "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:%23N718tNJHHA.3872 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Have I found a bug in DEC SP2? Possibly. I know MS are working hard on Maint Plans in SP2, so if you can create a repro (specify the steps needed), you probably want to post this to http://connect.microsoft.com/site/si...aspx?SiteID=68 -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:OiFy8tIJHHA.3952 (AT) TK2MSFTNGP02 (DOT) phx.gbl... The new SP2 Dec CTP does give a lot more info when I try to run this. It complains about sp_maintplan_logentry. It mentions the Execute permission on this msdb stored proc was denied. Have I found a bug in DEC SP2? Thanks Chris "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:uVPZH$EJHHA.1816 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Thanks again Tibor. I had looked in SSIS at the package and it had mentioned the Reader Role as db_dtsadmin, db_dtsoperator and the creator of the package. I can see as long as I give the proxy account one of the 2 db_dts roles in msdb it can read/execute the package. I suppose I could change this to some other msdb role and make sure the proxy account is a member of that role. I cannot see much about the error. It throws an Event 12291 in the Event log saying the package has failed. This is running SQL2005 SP1. Thanks Chris "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:eQXzvVBJHHA.960 (AT) TK2MSFTNGP04 (DOT) phx.gbl... So the backup is produced but you get package failed. I suggest you post this to an SSIS group. Next step would be to investigate why the SSIS package fails, looking at SSIS log etc, but since I'm no SSIS person, I don't have much to offer, I'm afraid... -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:ODu23y7IHHA.420 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Tibor, I first granted Backup Database and then Backup Log to the proxy account and added it to the application database in the db_backupoperator role. It appears to perform the backup but still says package failed. Any ideas? Thanks Chris "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:u6XuY86IHHA.3952 (AT) TK2MSFTNGP02 (DOT) phx.gbl... The login in question need to have a user in the database you want to backup and in that database be db_backupoperator or have granted the BACKUP DATABASE and/or BACKUP LOG privileges. I don't think that there's a privilege you can grant to a login that allow you to backup any database, less than CONTROL (same as sysadmin). At least, that is how I read below: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/499e5ed6-945c-4791-ab45-68dec0b9c289.htm -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:%23uMeVy6IHHA.5104 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Hi, We want to use the proxy account to take backups created from maintenance plans in SQL2005. What permissions does the proxy account need to do this? It has permissions to run SSIS packages and it failed saying the package could not be loaded. I then added the proxy account to the db_dtsadmin role and now I get the package execution failed. Does it also need the master role db_backupoperator to run? Thanks Chris |
#8
| |||
| |||
|
|
Have I found a bug in DEC SP2? Possibly. I know MS are working hard on Maint Plans in SP2, so if you can create a repro (specify the steps needed), you probably want to post this to http://connect.microsoft.com/site/si...aspx?SiteID=68 -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:OiFy8tIJHHA.3952 (AT) TK2MSFTNGP02 (DOT) phx.gbl... The new SP2 Dec CTP does give a lot more info when I try to run this. It complains about sp_maintplan_logentry. It mentions the Execute permission on this msdb stored proc was denied. Have I found a bug in DEC SP2? Thanks Chris "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:uVPZH$EJHHA.1816 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Thanks again Tibor. I had looked in SSIS at the package and it had mentioned the Reader Role as db_dtsadmin, db_dtsoperator and the creator of the package. I can see as long as I give the proxy account one of the 2 db_dts roles in msdb it can read/execute the package. I suppose I could change this to some other msdb role and make sure the proxy account is a member of that role. I cannot see much about the error. It throws an Event 12291 in the Event log saying the package has failed. This is running SQL2005 SP1. Thanks Chris "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:eQXzvVBJHHA.960 (AT) TK2MSFTNGP04 (DOT) phx.gbl... So the backup is produced but you get package failed. I suggest you post this to an SSIS group. Next step would be to investigate why the SSIS package fails, looking at SSIS log etc, but since I'm no SSIS person, I don't have much to offer, I'm afraid... -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:ODu23y7IHHA.420 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Tibor, I first granted Backup Database and then Backup Log to the proxy account and added it to the application database in the db_backupoperator role. It appears to perform the backup but still says package failed. Any ideas? Thanks Chris "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com wrote The login in question need to have a user in the database you want to backup and in that database be db_backupoperator or have granted the BACKUP DATABASE and/or BACKUP LOG privileges. I don't think that there's a privilege you can grant to a login that allow you to backup any database, less than CONTROL (same as sysadmin). At least, that is how I read below: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/499e5ed6-945c-4791-ab45-68dec0b9c289.htm -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:%23uMeVy6IHHA.5104 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Hi, We want to use the proxy account to take backups created from maintenance plans in SQL2005. What permissions does the proxy account need to do this? It has permissions to run SSIS packages and it failed saying the package could not be loaded. I then added the proxy account to the db_dtsadmin role and now I get the package execution failed. Does it also need the master role db_backupoperator to run? Thanks Chris |
#9
| |||
| |||
|
|
Tibor and others. The Dec SP2 CTP version gives a lot of debug info rather than just a generic package failure. It first complained about access to a stored procedure in msdb and then a table in msdb. Making the proxy account a db_owner in msdb helps (no way the right approach). Then it complained about the old backup delete sp in master. Again making the proxy account a db_owner in master does not solve the problem (even worse solution than doing this in msdb). It is all documented in Connect though no response from MS yet. See the SP2 DEC CTP Backup Error entry dated Dec 21st in Connect. Chris "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:%23N718tNJHHA.3872 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Have I found a bug in DEC SP2? Possibly. I know MS are working hard on Maint Plans in SP2, so if you can create a repro (specify the steps needed), you probably want to post this to http://connect.microsoft.com/site/si...aspx?SiteID=68 -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:OiFy8tIJHHA.3952 (AT) TK2MSFTNGP02 (DOT) phx.gbl... The new SP2 Dec CTP does give a lot more info when I try to run this. It complains about sp_maintplan_logentry. It mentions the Execute permission on this msdb stored proc was denied. Have I found a bug in DEC SP2? Thanks Chris "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:uVPZH$EJHHA.1816 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Thanks again Tibor. I had looked in SSIS at the package and it had mentioned the Reader Role as db_dtsadmin, db_dtsoperator and the creator of the package. I can see as long as I give the proxy account one of the 2 db_dts roles in msdb it can read/execute the package. I suppose I could change this to some other msdb role and make sure the proxy account is a member of that role. I cannot see much about the error. It throws an Event 12291 in the Event log saying the package has failed. This is running SQL2005 SP1. Thanks Chris "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com wrote So the backup is produced but you get package failed. I suggest you post this to an SSIS group. Next step would be to investigate why the SSIS package fails, looking at SSIS log etc, but since I'm no SSIS person, I don't have much to offer, I'm afraid... -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:ODu23y7IHHA.420 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Tibor, I first granted Backup Database and then Backup Log to the proxy account and added it to the application database in the db_backupoperator role. It appears to perform the backup but still says package failed. Any ideas? Thanks Chris "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com wrote The login in question need to have a user in the database you want to backup and in that database be db_backupoperator or have granted the BACKUP DATABASE and/or BACKUP LOG privileges. I don't think that there's a privilege you can grant to a login that allow you to backup any database, less than CONTROL (same as sysadmin). At least, that is how I read below: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/499e5ed6-945c-4791-ab45-68dec0b9c289.htm -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Wood" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message news:%23uMeVy6IHHA.5104 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Hi, We want to use the proxy account to take backups created from maintenance plans in SQL2005. What permissions does the proxy account need to do this? It has permissions to run SSIS packages and it failed saying the package could not be loaded. I then added the proxy account to the db_dtsadmin role and now I get the package execution failed. Does it also need the master role db_backupoperator to run? Thanks Chris |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.