SSIS - DTS - Agent Permissions

http://technet.microsoft.com/en-us/library/ms188283.aspx SQL Server Agent Fixed Database Roles SQL Server 2012 Other Versions 3 out of 8 rated this helpful - Rate this topic SQL Server 2005 introduced the following msdb database fixed database roles, which give administrators finer control over access to SQL Server Agent. The roles listed from least to most privileged access are: SQLAgentUserRole SQLAgentReaderRole SQLAgentOperatorRole When users who are not members of one of these roles are connected to SQL Server in SQL Server Management Studio, the SQL Server Agent node in Object Explorer is not visible. A user must be a member of one of these fixed database roles or a member of the sysadmin fixed server role to use SQL Server Agent. Permissions of SQL Server Agent Fixed Database Roles The SQL Server Agent database role permissions are concentric in relation to one another -- more privileged roles inherit the permissions of less privileged roles on SQL Server Agent objects (including alerts, operators, jobs, schedules, and proxies). For example, if members of least-privileged SQLAgentUserRole have been granted access to proxy_A, members of both SQLAgentReaderRole and SQLAgentOperatorRole automatically have access to this proxy even though access to proxy_A has not been explicitly granted to them. This may have security implications, which are discussed in the following sections about each role. SQLAgentUserRole Permissions SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own. They cannot use multiserver jobs (master and target server jobs), and they cannot change job ownership to gain access to jobs that they do not already own. SQLAgentUserRole members can view a list of available proxies only in the Job Step Properties dialog box of SQL Server Management Studio. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of SQLAgentUserRole. Security noteSecurity Note Consider the security implications before granting proxy access to members of the SQL Server Agentdatabaseroles. The SQLAgentReaderRole and the SQLAgentOperatorRole are automatically members of the SQLAgentUserRole. This means that members of SQLAgentReaderRole and SQLAgentOperatorRole have access to all SQL Server Agent proxies that have been granted to the SQLAgentUserRole and can use those proxies. The following table summarizes SQLAgentUserRole permissions on SQL Server Agent objects. 1 Cannot change job ownership. 2 Can get list of available operators for use in sp_notify_operator and the Job Properties dialog box of Management Studio. 3 List of proxies only available in the Job Step Properties dialog box of Management Studio. 4 Members of SQLAgentUserRole must explicitly be granted the EXECUTE permission on sp_purge_jobhistory to delete job history on jobs that they own. They cannot delete job history for any other jobs. SQLAgentReaderRole Permissions SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own. SQLAgentReaderRole members cannot change job ownership to gain access to jobs that they do not already own. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of the SQLAgentReaderRole. Security noteSecurity Note Consider the security implications before granting proxy access to members of the SQL Server Agentdatabaseroles. Members of SQLAgentReaderRole are automatically members of the SQLAgentUserRole. This means that members of SQLAgentReaderRole have access to all SQL Server Agent proxies that have been granted to SQLAgentUserRole and can use those proxies. 1 Cannot change job ownership. 2 Can get list of available operators for use in sp_notify_operator and the Job Properties dialog box of Management Studio. 3 List of proxies only available in the Job Step Properties dialog box of Management Studio. 4 Members of SQLAgentReaderRole must explicitly be granted the EXECUTE permission on sp_purge_jobhistory to delete job history on jobs that they own. They cannot delete job history for any other jobs. SQLAgentOperatorRole Permissions SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server. SQLAgentOperatorRole members have additional permissions on local jobs and schedules. They can execute, stop, or start all local jobs, and they can delete the job history for any local job on the server. They can also enable or disable all local jobs and schedules on the server. To enable or disable local jobs or schedules, members of this role must use the stored procedures sp_update_job and sp_update_schedule. Only the parameters that specify the job or schedule name or identifier and the @enabled parameter can be specified by members of SQLAgentOperatorRole. If they specify any other parameters, execution of these stored procedures fails. SQLAgentOperatorRole members cannot change job ownership to gain access to jobs that they do not already own. The Jobs, Alerts, Operators, and Proxies nodes in SQL Server Management Studio Object Explorer are visible to members of SQLAgentOperatorRole. Only the Error Logs node is not visible to members of this role. Security noteSecurity Note Consider the security implications before granting proxy access to members of the SQL Server Agentdatabaseroles. Members of SQLAgentOperatorRole are automatically members of SQLAgentUserRole and SQLAgentReaderRole. This means that members of SQLAgentOperatorRole have access to all SQL Server Agent proxies that have been granted to either SQLAgentUserRole or SQLAgentReaderRole and can use those proxies. 1 Can get list of available operators for use in sp_notify_operator and the Job Properties dialog box of Management Studio. 2 Cannot change job ownership. 3SQLAgentOperatorRole members can enable or disable local jobs they do not own by using the stored procedure sp_update_job and specifying values for the @enabled and the @job_id (or @job_name) parameters. If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail. 4SQLAgentOperatorRole members can enable or disable schedules they do not own by using the stored procedure sp_update_schedule and specifying values for the @enabled and the @schedule_id (or @name) parameters. If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail. Assigning Users Multiple Roles Members of the sysadmin fixed server role have access to all SQL Server Agent functionality. If a user is not a member of the sysadmin role, but is a member of more than one SQL Server Agent fixed database role, it is important to remember the concentric permissions model of these roles. Because more privileged roles always contain all the permissions of less privileged roles, a user who is a member of more than one role automatically has the permissions associated with the most privileged role that the user is a member of.

All Variables Named After Food

http://jacquesmattheij.com/The+worst+program+I+ever+worked+on Not trying to boost content, just love this tale and do not want to loose it... ------------------------------------------------------------------------------------------- The Worst Program I Ever Worked On Mar 16th, 2011 Most contract jobs fade pretty quickly in memory after the work is done, but some you remember for the rest of your life. This is one of the latter variety. This happened long ago, at a (fair sized) company that shall remain nameless. The software was a chunk of code that had been maintained by a single guy that had been fired recently and was a core component of a commercial system. So far nothing unusual, companies tend to find out that they have a piece of critical knowledge in one head all the time, usually if something happens to or with such a person there is a mild panic, some days of frantic reading and then life continues. Not so in this case. Some bug had popped up in this program and the guy assigned to fix it had come back alternatively laughing and crying babbling nonsense about ‘pizzas calling hamburgers and passing booze’. The programmer that had written this code had his own sense of humor and his own ideas about the term ‘job security’. We’ve all heard the tall stories about the accounting package that wipes all records if there is no payment to some off-shore bankaccount 48 hours after terminating some tech guy, but such tricks are relatively easy to deal with - assuming that most of those stories are true, which I have a hard time believing, and I’ve never seen any of those in real life. The software this guy left behind did not have any logic bombs or other nasty tricks in it, it compiled just fine, and besides that one bug it seemed to work fine as well. Imagine this though: every function and variable name in the program was named after food. Pizza’s, tomatoes, pickles, various kinds of cheese, fruits, vegetables, drinks and so on, for page after endless page. The only place where the names made any immediate sense was ‘main’ and any C stdlib calls. So I got handed the thankless job of working very hard to get the program back to a state where it could be maintained. It was a really nifty form of encryption, and only with a key in hand would the salad of code make any sense. Little by little I converted the program back by naming the functions and the variables with more sensible names, and as the work progressed it got easier and easier. Working backwards from a known function and source code is a lot easier than reverse assembly of unknown code (because then you first have to separate code/data and you have to figure out what the high level representation was, here I had the high level representation in plaintext in front of me), so it wasn’t that the job was impossible or even particularly hard, it was just tedious. Once a function or variable had been identified to have some probable meaning a new name was made and a search-and-replace took care of the actual renaming. Another problem was that it was bad code, in fact, the various spaghetti twists in the code did more to obfuscate the meaning of it than the lack of meaningful symbols, so once I had all the functions and variables renamed back to something that made sense I re-wrote a good bit of the code to make it easier to understand and work more efficient. I never did find out if he had a non-obfuscated version of the code that he ran through a ‘blender’ script that obfuscated the original code by stripping out any and all comments and replacing all symbols by nonsensical ones. I find it hard to get in to the mind of someone that does something like that to begin with but I find it even harder to imagine that he wrote code like that directly, that would have actually been an amazing feat. Of course, if in your head you think that they can’t fire you because your program makes no sense (or that they might hire you back) then you’re delusional so whatever goal this guy had with his trick it failed in a terrible way (I can’t imagine him giving his former employer as a reference either) but it made for an amusing couple of weeks, and a very happy customer.

Bulk Insert

UtilSQL.DML.DBExecNonQueryText("TRUNCATE TABLE foo;", "bar"); using (SqlDataReader portal = UtilSQL.DML.DBExecReader("EXEC myproc", "mate")) { using (SqlConnection conn = UtilSQL.DML.GetSQLConnString("bar")) { conn.Open(); /// Note: for SqlBulkCopy to work the columns must be identically named /// and typed and all fields in the record must be copied or else you /// get a useless error about "LocaleID" /// /// Turns out that using SQLBulkCopy in the middle tier is faster than an /// insert using linked servers even if the data is prepped in a temp table /// first SqlBulkCopy bulk = new SqlBulkCopy(conn); bulk.DestinationTableName = "destTable"; // UID Username LastName FirstName Email isAdmin Inactive CostCenter Descr Bud_Read Bud_Write Facility SqlBulkCopyColumnMapping mapping1 = new SqlBulkCopyColumnMapping("UID", "UID"); SqlBulkCopyColumnMapping mapping2 = new SqlBulkCopyColumnMapping("Username", "Username"); SqlBulkCopyColumnMapping mapping3 = new SqlBulkCopyColumnMapping("LastName", "LastName"); SqlBulkCopyColumnMapping mapping4 = new SqlBulkCopyColumnMapping("FirstName", "FirstName"); SqlBulkCopyColumnMapping mapping5 = new SqlBulkCopyColumnMapping("Email", "Email"); SqlBulkCopyColumnMapping mapping6 = new SqlBulkCopyColumnMapping("isAdmin", "isAdmin"); SqlBulkCopyColumnMapping mapping7 = new SqlBulkCopyColumnMapping("Inactive", "Inactive"); SqlBulkCopyColumnMapping mapping8 = new SqlBulkCopyColumnMapping("CostCenter", "CostCenter"); SqlBulkCopyColumnMapping mapping9 = new SqlBulkCopyColumnMapping("Descr", "Descr"); SqlBulkCopyColumnMapping mapping10 = new SqlBulkCopyColumnMapping("Bud_Read", "Bud_Read"); SqlBulkCopyColumnMapping mapping11 = new SqlBulkCopyColumnMapping("Bud_Write", "Bud_Write"); SqlBulkCopyColumnMapping mapping12 = new SqlBulkCopyColumnMapping("Facility", "Facility"); bulk.ColumnMappings.Add(mapping1); bulk.ColumnMappings.Add(mapping2); bulk.ColumnMappings.Add(mapping3); bulk.ColumnMappings.Add(mapping4); bulk.ColumnMappings.Add(mapping5); bulk.ColumnMappings.Add(mapping6); bulk.ColumnMappings.Add(mapping7); bulk.ColumnMappings.Add(mapping8); bulk.ColumnMappings.Add(mapping9); bulk.ColumnMappings.Add(mapping10); bulk.ColumnMappings.Add(mapping11); bulk.ColumnMappings.Add(mapping12); bulk.WriteToServer(portal); bulk.Close(); conn.Close(); recCountPortal = UtilSQL.DML.DBExecScalarInt64("SELECT COUNT (*) FROM destTable", "bar"); } } this.lblPortal.Text = recCountPortal.ToString() + " records from portal.";